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:
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.
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