dilluns, 24 d’abril de 2017

Desplegament automàtic de paquets SSIS

Avui veurem un sistema per pujar ETLs en la versió SQL Server 2014 que estiguin en project deployment a través de sentències SQL.
Aquest sistema és molt més ràpid que el deploy de projectes a través de SSDT, sobretot quan hi ha molts projectes ja desplegats.

També permet no haver de donar permisos de despegament de projectes a tots els developers ja que l'usuari que desplegarà quedarà encapsulat en el job d'SQL.

El procediment funcionarà de la següent manera:
Tindrem una carpeta on es deixaran els .ispac a desplegar i els .sql amb el codi de modificació de BBDD i amb les configuracions d'environments. Aquesta carpeta serà accessible des del servidor d'SQL Server.
Tindrem una taula on registrarem els .ispac que s'han de desplegar, juntament amb scripts d'alter de BBDD i configruació d'environments.
Hi haurà un job d'SQL Server que s'executarà com un daemon i anirà mirant la taula per si hi ha projectes pendents de desplegar.
Per cada ispac s'executarà el procediment i registrarà si ha acabat correctament o no.


La taula on registrar les dades seria similar a:
    CREATE TABLE [dbo].[ispac_upload](
        [id] [int] IDENTITY(1,1) NOT NULL primary key,
        [ispac_file] [varchar](100) NOT NULL,
        [upload_user] [varchar](100) NOT NULL DEFAULT (user_name()),
        [update_description] [varchar](4000) NOT NULL,
        [script_alter_bbdd] [varchar](100) NULL,
        [script_environments] [varchar](100) NULL,
        [status] [int] NOT NULL DEFAULT ((0)),
        [insert_date] [datetime2](0) NOT NULL DEFAULT (getdate()),
        [update_date] [datetime2](0) NOT NULL DEFAULT (getdate())
    )


L'insert seria del tipus:
    insert into ispac_upload(ispac_file,  update_description, script_alter_bbdd, script_environments) values ('Projecte.ispac', 'Pujo un projecte amb modificacions', 'alters_proj.sql', 'environments_proj.sql')

Per generar un .ispac es pot fer amb el SSDT amb botò dret al projecte --> Build

  
    El procediment primer haurà de validar que es poden obrir els fitxers i després executarà cadascun dels passos.
Amb: Set @ispacPathScript = ' (SELECT @ProjectBinary=bulkColumn FROM OPENROWSET(BULK '''+ @path+@ispac_file+''', SINGLE_BLOB) as BinaryData)'
Obrirem el fitxer ispac que està al path indicat i deixarem el contingut a la variable @ProjectBinary

    Set @ispacPathScript = ' (SELECT @ProjectBinary=bulkColumn FROM OPENROWSET(BULK '''+ @path+@ispac_file+''', SINGLE_BLOB) as BinaryData)'
    begin try
        exec sp_executesql @ispacPathScript, N'@ProjectBinary VARBINARY(MAX) OUTPUT',@ProjectBinary =@ProjectBinary OUTPUT
    end try
    begin catch
        set @status = 2
        update ispac_upload set status=@status, error_description='ERROR LECTURA FITXER ISPAC: '+ERROR_MESSAGE(), update_date=getdate()
        where id=@id
    end catch

   
Farem el mateix amb els fitxers .sql, l'única diferència és el tipus de dades de la variable retornada que serà nvarchar.

Un cop tenim el codi recuperat l'executarem.
Per als .sql farem servir sp_executesql juntament amb la variable on hem recuperat el codi SQL. És molt important no posar GO dins del codi SQL, ja que sp_executesql no els accepta i ens donarà error.
    exec sp_executesql @alterDatabaseBinary
Per al fitxer .ispac invocarem a la funció ssisdb.catalog.deploy_project
    DECLARE @operation_id as bigint
    DECLARE @ProjectStream as varbinary(max)
    Exec ssisdb.catalog.deploy_project @folder_name = 'PRO', @project_name = @projectName, @Project_Stream = @ProjectBinary, @operation_id = @operation_id out


I ja tindrem el nostre projecte desplegat automàticament!

L'script de configuració d'environments és un pel més complicat de fer que l'alter de BBDD que ho acostumem a tenir més per la mà. Us passo una petita referència:
declare @cnt int
1) validem si la variable ja existeix
select @cnt = count(*)
from ssisdb.internal.environment_variables
where name='PathDeployJob'


2) Si no existeix la registrem
if @cnt=0 begin
    EXECUTE  ssisdb.[catalog].[create_environment_variable]
       @folder_name='PRO'
      ,@environment_name='PRO'
      ,@variable_name='PathDeployJob'
      ,@data_type=N'String'
      ,@sensitive=0
      ,@value=N'C:\path\exempe'
      ,@description='Path exemple'
  end


3) Validem si el projecte ja té referenciat un environment 
select @cnt=count(*)
from ssisdb.[internal].[environment_references] er join ssisdb.internal.projects p on er.project_id=p.project_id
where p.name='ProjectName'
and environment_name='PRO'


4) Si no està referenciat, el referenciem
if @cnt=0 begin
    Declare @reference_id bigint
    EXEC [SSISDB].[catalog].[create_environment_reference] @environment_name=N'PRO', @reference_id=@reference_id OUTPUT, @project_name=N'ProjectName', @folder_name=N'PRO', @reference_type=R
end
 

5) Associem el Projecte (ispac) amb la variable de l'environment.
EXEC [SSISDB].[catalog].[set_object_parameter_value] @object_type=20, @parameter_name=N'PathDeployJob', @object_name=N'ProjectName', @folder_name=N'PRO', @project_name=N'ProjectName', @value_type=R, @parameter_value=N'PathDeployJob'

Aquí teniu el codi complert del procedure per a desplegar ispacs. Si l'invoqueu des d'un job d'SQL Server com un daemon el deplegament serà automàtic cada cop que es faci un insert a la taula de control.   


create procedure [dbo].[sp_automaticDeploy] @path varchar(100)
as

declare @id int
declare @ispac_file varchar(100)
declare @server varchar(100)
declare @script_alter_bbdd varchar(100)
declare @script_environments varchar(100)

DECLARE cur CURSOR FOR  
SELECT id, ispac_file, server, script_alter_bbdd, script_environments 
FROM ispac_upload
where status=0

OPEN cur 
FETCH NEXT FROM cur INTO @id, @ispac_file, @server, @script_alter_bbdd, @script_environments

WHILE @@FETCH_STATUS = 0  
BEGIN  
    Declare @status int  
 
 Declare @projectName varchar(100)
 
 Declare @ispacPathScript nvarchar(1000)
 Declare @alterDatabasePathScript nvarchar(1000)
 Declare @environmentPathScript nvarchar(1000)
 
 Declare @ProjectBinary varbinary(max)
 Declare @alterDatabaseBinary nvarchar(max)
 Declare @environmentBinary nvarchar(max)

 Set @status =0 --iniciem status a 0 (no processat)
 set @projectName = replace(@ispac_file, '.ispac','')

 --Fitxer ispac
 DECLARE @Handler AS INTEGER

 Set @ispacPathScript = ' (SELECT @ProjectBinary=bulkColumn FROM OPENROWSET(BULK '''+ @path+@ispac_file+''', SINGLE_BLOB) as BinaryData)'
 begin try
  exec sp_executesql @ispacPathScript, N'@ProjectBinary VARBINARY(MAX) OUTPUT',@ProjectBinary =@ProjectBinary OUTPUT
 end try
 begin catch
  set @status = 2
  update ispac_upload set status=@status, error_description='ERROR LECTURA FITXER ISPAC: '+ERROR_MESSAGE(), update_date=getdate()
  where id=@id 
 end catch

 
 --fitxer Alter BBDD
 if @status=0 and @script_alter_bbdd is not null begin
  Set @alterDatabasePathScript = 'Set @alterDatabaseBinary = (SELECT * FROM OPENROWSET(BULK '''+ @path+@script_alter_bbdd+''', SINGLE_CLOB) as BinaryData)'
  begin try
   exec sp_executesql @alterDatabasePathScript, N'@alterDatabaseBinary nvarchar(MAX) OUTPUT',@alterDatabaseBinary =@alterDatabaseBinary OUTPUT
  end try
  begin catch
   set @status = 2
   update ispac_upload set status=@status, error_description='ERROR LECTURA FITXER ALTER BBDD: '+ERROR_MESSAGE(), update_date=getdate()
   where id=@id 
  end catch
 end


 --fitxer configuració environments
 if @status=0 and @script_environments is not null begin
  Set @environmentPathScript = 'Set @environmentBinary = (SELECT * FROM OPENROWSET(BULK '''+ @path+@script_environments+''', SINGLE_CLOB) as BinaryData)'
  begin try
   exec sp_executesql @environmentPathScript, N'@environmentBinary nvarchar(MAX) OUTPUT',@environmentBinary =@environmentBinary OUTPUT
  end try
  begin catch
   set @status = 2
   update ispac_upload set status=@status, error_description='ERROR LECTURA FITXER ENVIRONMENTS: '+ERROR_MESSAGE(), update_date=getdate()
   where id=@id 
  end catch
 end

 
 --EXECUTEM fitxer Alter BBDD
 if @status=0 and @script_alter_bbdd is not null begin
  begin try
   exec sp_executesql @alterDatabaseBinary
  end try
  begin catch
   set @status = 2
   update ispac_upload set status=@status, error_description='ERROR EXECUCIÓ FITXER ALTER BBDD: '+ERROR_MESSAGE(), update_date=getdate()
   where id=@id 
  end catch
 end
 
 --Pugem fitxer ispac
 if @status=0 begin
  begin try
   DECLARE @operation_id as bigint
   DECLARE @ProjectStream as varbinary(max)
   Exec ssisdb.catalog.deploy_project @folder_name = 'PRO', @project_name = @projectName, @Project_Stream = @ProjectBinary, @operation_id = @operation_id out
  end try
  begin catch
   set @status = 2
   update ispac_upload set status=@status, error_description='ERROR PUJADA FITXER ISPAC: '+ERROR_MESSAGE(), update_date=getdate()
   where id=@id 
  end catch
 end
 

 --fitxer configuració environments
 if @status=0 and @script_environments is not null begin
  begin try
   exec sp_executesql @environmentBinary
  end try
  begin catch
   set @status = 2
   update ispac_upload set status=@status, error_description='ERROR EXECUCIÓ FITXER ENVIRONMENTS: '+ERROR_MESSAGE(), update_date=getdate()
   where id=@id 
  end catch
 end

 if @status=0 begin
   set @status = 1
   update ispac_upload set status=@status, update_date=getdate() 
   where id=@id 
 end
 

 FETCH NEXT FROM cur INTO @id, @ispac_file, @server, @script_alter_bbdd, @script_environments
END  

CLOSE cur  
DEALLOCATE cur
GO

Cap comentari:

Publica un comentari a l'entrada