Introduction
A blogpost of
Hans Michiels caught my attention when I was looking on how to deploy SSIS packages to the SSIS Catalog in SQL Server in a standard manner. The problem I had was that it was bit time consuming to deploy SSIS packages and I wanted to create projects, environments and environment variables on the fly from a script.
This blogpost and this stored procedure is written for a particular situation and I don't suggest that you can use it in every situation. In my situation it works perfectly but no guarantees that it will work in every situation. Test it and try it your self (and not in a production environment ;-) )
Used model
For this script, I've used the following model to understand the working of the SSIS catalog and Visual Studio SSIS project. On the left the Visual studio SSIS project is shown with the project "Project" and the parameters "Parameter1" and "Parameter2".
On the right hand of the picture, the SSIS Catalog with a folder, the project and the environment is depicted. The project in the SSIS catalog is a deployed project of the VS SSIS project. The deployed project in the SSIS catalog is connected with the environment and the parameters and environment variables are also connected.
Projectparameters
For this blog post I created a couple of project parameters in the SSIS Visual Studio project. A couple of connectionstrings that I pass to the connection mangers in the SSIS project.
The Stored Procedure
In the box below, I've copied the stored procedure that I've created and there are a couple of things to keep in mind. First, the parameters of the stored procedure are important. These parameters controls the behavior of the stored procedure:
- @folder_name : the name of the folder in the SSIS catalog. Anything is possible.
- @environment_name : The name of the environment (DEV or TEST)
- @environment_description : a free text description.
- @project_name : This is the name of the SSISproject as entered in the properties in the VS project. This is important because this is needed for deploying of the ispac file (built VS SSIS project).
- @ispacpath : The path and the name of the built ispac file.
- @SQLServerName : This parameter is used in the connectionstrings that are passed to the connectionmanagers in the SSIS packages. Working with @@Servername is handy but not always use able when working on multiple systems.
- @projectparam1. This could be another setting that you want to change in case you deploy the SSIS project to a server.
The stored procedure code is copied in the box below:
CREATE PROCEDURE dbo.usp_CreateAndDeploySSISProject
@folder_name AS sysname,
@environment_name AS nvarchar(128),
@environment_description AS nvarchar(128),
@project_name AS nvarchar(128),
@ispacpath AS nvarchar(500) ,
@SQLServerName AS nvarchar(100),
@projectparam1 AS nvarchar(100)
AS
BEGIN
--------------------------------------------------------------------------------
--Variables Section
--------------------------------------------------------------------------------
DECLARE @environment_variables_table TABLE(
[id] int identity(1, 1),
[variable_name] nvarchar(128),
[data_type] nvarchar(128),
[sensitive] bit,
[value] nvarchar(4000),
[description] nvarchar(1024)
)
DECLARE @object_parameter_value_table TABLE(
[id] int identity(1, 1),
[object_type] smallint,
[object_name] nvarchar(260),
[parameter_name] nvarchar(128),
[parameter_value] sql_variant,
[value_type] char(1)
)
DECLARE @id int
DECLARE @max_id int
-- DECLARE @folder_name sysname
-- DECLARE @environment_name nvarchar(128)
-- DECLARE @environment_description nvarchar(128)
-- DECLARE @project_name nvarchar(128)
DECLARE @variable_name nvarchar(128)
DECLARE @data_type nvarchar(128)
DECLARE @sensitive bit
DECLARE @value sql_variant
DECLARE @value_tinyint tinyint
DECLARE @description nvarchar(1024)
DECLARE @nsql nvarchar(max)
DECLARE @object_type smallint
DECLARE @object_name nvarchar(260)
DECLARE @value_type CHAR(1)
DECLARE @parameter_name nvarchar(128)
DECLARE @parameter_value sql_variant
-- DECLARE @ProjectBinary as varbinary(max)
DECLARE @operation_id as bigint
DECLARE @ispacbinary as varbinary(max)
-- DECLARE @ispacpath as nvarchar(500)
-- DECLARE @servername as nvarchar(100)
--------------------------------------------------------------------------------
--Setting the variables
--------------------------------------------------------------------------------
--SET @folder_name = N'SSISBIFolder5'
--SET @environment_name = N'DEV'
--SET @environment_description = N'Development environment'
--SET @project_name=N'SSISBI'
--SET @ispacpath = 'D:\SSISProject\.....\SSISBI.ispac'
IF @folder_name IS NULL OR
@environment_name IS NULL OR
@environment_description IS NULL OR
@project_name IS NULL OR
@ispacpath IS NULL OR
@projectparam1 IS NULL
BEGIN
Print 'One (or more) parameters is/are empty!! Aborting'
RETURN
END
--SET @SQLservername = CONVERT(sysname, SERVERPROPERTY('servername'))
--------------------------------------------------------------------------------
-- Create Folder
--------------------------------------------------------------------------------
PRINT '--------------------------------------------------------------------------------'
PRINT 'Creating folder ' + @folder_name
PRINT '--------------------------------------------------------------------------------'
IF NOT EXISTS (
SELECT 1
FROM [SSISDB].[internal].[folders]
WHERE [name] = @folder_name
)
BEGIN
SET @nsql = N'[SSISDB].[catalog].[create_folder] N''' + @folder_name + ''';'
PRINT @nsql;
EXEC sp_executesql @nsql;
END
ELSE
BEGIN
PRINT 'Not able to (re) create folder ' + @folder_name + ' because it already exists'
END
PRINT CHAR(13)
--------------------------------------------------------------------------------
--Create the environment and add the variables.
--------------------------------------------------------------------------------
PRINT '--------------------------------------------------------------------------------'
PRINT 'Creating Environment ' + @environment_name + ' in the folder ' + @folder_name
PRINT '--------------------------------------------------------------------------------'
IF NOT EXISTS (
SELECT 1
FROM [SSISDB].[internal].[environments] E
INNER JOIN [SSISDB].[internal].[folders] F ON E.folder_id = F.folder_id
WHERE [environment_name] = @environment_name
AND F.[name] = @folder_name
)
BEGIN
SET @nsql = N'[SSISDB].[catalog].[create_environment] N''' + @folder_name + ''',N''' +
@environment_name + ''',N''' + @environment_description + ''';'
PRINT @nsql;
EXEC sp_executesql @nsql;
END
ELSE
BEGIN
PRINT 'Not able to (re) create Environment ' + @environment_name + ' because it already exists in the folder ' + @folder_name
END
PRINT CHAR(13)
--------------------------------------------------------------------------------
-- Delete and add the environment variables to the Environment
--------------------------------------------------------------------------------
PRINT '--------------------------------------------------------------------------------'
PRINT 'Deleting and adding Environmentvariables in ' + @environment_name
PRINT '--------------------------------------------------------------------------------'
INSERT INTO @environment_variables_table
( [variable_name], [data_type], [sensitive], [value], [description] )
VALUES
( N'envMartConnectionString', N'String', 0, N'Data Source=' + @SQLservername + ';Initial Catalog=Mart;Provider=SQLNCLI11.1;Integrated Security=SSPI;Auto Translate=False;', N'Connection string for Mart')
, ( N'envMetaConnectionString', N'String', 0, N'Data Source=' + @SQLservername + ';Initial Catalog=rMeta;Provider=SQLNCLI11.1;Integrated Security=SSPI;Auto Translate=False;', N'Connection string for Meta' )
, ( N'envStagingConnectionString', N'String', 0, N'Data Source=' + @SQLservername + ';Initial Catalog=Staging;Provider=SQLNCLI11.1;Integrated Security=SSPI;Auto Translate=False;', N'Connection string for Staging' )
, ( N'envSourceConnectionString', N'String', 0, N'USER=<user> <Somevariables> <HOST>=' + @projectparam1 + ' PASSWD=!@#@#!@%%$%', N'Connection string for source' )
SELECT @id = 1, @max_id = MAX([id]) FROM @environment_variables_table
WHILE @id <= @max_id
BEGIN
SELECT
@variable_name = v.variable_name,
@data_type = v.data_type,
@sensitive = v.sensitive,
@value = v.value,
@description = v.[description]
FROM @environment_variables_table v
WHERE [id] = @id;
IF EXISTS (
SELECT 1
FROM [SSISDB].[internal].[environment_variables] V
INNER JOIN [SSISDB].[internal].[environments] E ON E.environment_id = V.environment_id
INNER JOIN [SSISDB].[internal].[folders] F ON E.folder_id = F.folder_id
WHERE V.[name] = @variable_name
AND E.environment_name = @environment_name
AND F.[name] = @folder_name
)
BEGIN
SET @nsql = N'EXECUTE [SSISDB].[catalog].[delete_environment_variable] '
+ N'@folder_name = N'''+ @folder_name + ''', @environment_name = N''' + @environment_name + ''', '
+ N'@variable_name = N''' + @variable_name + ''''
PRINT @nsql;
EXEC sp_executesql @nsql;
END
--PRINT '/*'
--PRINT @variable_name
--PRINT CONVERT(nvarchar(128), SQL_VARIANT_PROPERTY(@value, 'BaseType'));
--PRINT '*/'
SET @nsql = N'EXECUTE [SSISDB].[catalog].[create_environment_variable] '
+ N'@folder_name = N'''+ @folder_name + ''', @environment_name = N''' + @environment_name + ''', '
+ N'@variable_name = N'''+ @variable_name + ''', @data_type = N''' + @data_type + ''', '
+ N'@sensitive = ' + CONVERT(NVARCHAR, @sensitive) + ', @description = N''' + @description + ''', '
+ CHAR(13) + CHAR(10) + N'@value = ' +
CASE UPPER(@data_type)
WHEN 'String' THEN 'N''' + CONVERT(NVARCHAR(1000), @value) + ''' '
ELSE CONVERT(NVARCHAR(1000), @value)
END + '; '
PRINT @nsql;
EXEC sp_executesql @nsql;
SET @id = @id + 1
END
PRINT CHAR(13)
--------------------------------------------------------------------------------
-- Deploy the SSIS project (ispac) in the SSIS Catalog
--------------------------------------------------------------------------------
PRINT '--------------------------------------------------------------------------------'
PRINT 'Deploy the ispac of the SSIS project in the SSIS Catalog '
PRINT '--------------------------------------------------------------------------------'
SET @nsql = 'SET @ispacBinary = (SELECT * FROM OPENROWSET(BULK N''' + @ispacpath + ''', SINGLE_BLOB) as BinaryData)'
print @nsql
EXEC SP_EXECUTESQL
@Query = @nsql
, @Params = N'@ispacBinary varbinary(max) OUTPUT'
, @ispacBinary = @ispacBinary OUTPUT
SET @nsql = N'EXECUTE [SSISDB].[catalog].[deploy_project] '
+ N'@folder_name = N'''+ @folder_name + ''', @project_name = N''' + @project_name + ''', '
+ N'@Project_Stream = @ispacBinary'
PRINT @nsql;
EXEC sp_executesql @nsql, N'@ispacBinary varbinary(MAX)', @ispacBinary = @ispacBinary;
PRINT CHAR(13)
--------------------------------------------------------------------------------
--Create the environment and add the variables.
--------------------------------------------------------------------------------
PRINT '--------------------------------------------------------------------------------'
PRINT 'Creating an environmentreference in ' + @environment_name
PRINT '--------------------------------------------------------------------------------'
Declare @reference_id bigint
IF NOT EXISTS(SELECT 1
FROM [SSISDB].[internal].[environment_references] R
INNER JOIN [SSISDB].[internal].[projects] P ON P.project_id = R.project_id
INNER JOIN [SSISDB].[internal].[folders] F ON P.folder_id = F.folder_id
WHERE P.name = @project_name
AND R.environment_name = @environment_name
AND F.[name] = @folder_name
)
BEGIN
SET @nsql = N'EXECUTE [SSISDB].[catalog].[create_environment_reference] '
+ N'@environment_name = N''' + @environment_name + ''', '
+ N'@reference_id = @reference_id , @project_name = N''' + @project_name + ''', '
+ N'@folder_name = N''' + @folder_name + ''', @reference_type = N''' + 'R' +''''
PRINT @nsql;
EXEC sp_executesql @nsql, N'@reference_id bigint', @reference_id = @reference_id;
END
PRINT CHAR(13)
--------------------------------------------------------------------------------
-- Connect the environment variables with the project parameters
--------------------------------------------------------------------------------
PRINT '--------------------------------------------------------------------------------'
PRINT 'Connect the environment variables with the project parameters'
PRINT '--------------------------------------------------------------------------------'
INSERT INTO @object_parameter_value_table (
[object_type],
[object_name],
[parameter_name],
[parameter_value],
[value_type]
)
VALUES
( 20, N'', N'MartConnectionString', N'envMartConnectionString', 'R')
, ( 20, N'', N'MetaConnectionString', N'envMetaConnectionString', 'R')
, ( 20, N'', N'StagingConnectionString', N'envStagingConnectionString', 'R')
, ( 20, N'', N'SourceConnectionString', N'envSourceConnectionString', 'R')
SELECT @id = 1, @max_id = MAX([id]) FROM @object_parameter_value_table
WHILE @id <= @max_id
BEGIN
SELECT
@object_type = v.[object_type],
@object_name = v.[object_name],
@parameter_name = v.[parameter_name],
@parameter_value = v.[parameter_value],
@value_type = v.[value_type]
FROM @object_parameter_value_table v
WHERE [id] = @id;
SET @value_type = 'R'
--SET @parameter_value = @parameter_name;
SET @nsql = N'EXECUTE [SSISDB].[catalog].[set_object_parameter_value]'
+ N'@object_type = ''' + CAST(@object_type AS nvarchar(10)) + ''', '
+ N'@folder_name = N''' + @folder_name + ''', '
+ N'@project_name = N''' + @project_name + ''', '
+ N'@parameter_name = N''' + @parameter_name + ''', '
+ N'@parameter_value = N''' + CAST(@parameter_value AS Nvarchar(100)) + ''', '
+ N'@object_name = N''' + @object_name + ''', '
+ N'@value_type = N''' + @value_type + ''';'
PRINT @nsql;
EXEC sp_executesql @nsql, N'@reference_id bigint', @reference_id = @reference_id;
SET @id = @id + 1
END
PRINT CHAR(13)
PRINT '--------------------------------------------------------------------------------'
PRINT 'Done'
PRINT '--------------------------------------------------------------------------------'
END
Deploying a SSIS project to the SSIS Catalog
Now let's try this stored procedure and see what happens. Don't forget to make a note of the
Name of the SSISProject. That name is needed as a parameter for the stored procedure.
The next step is to build the project. This will create a new ispac file and the location of the ispac file is used in the stored procedure. Store the location of the ispac file in notepad.
Creating a SSIS project in a SSISfolder
First let's show how the SSIS catalog looks like.
This is the execution code of the Stored Procedure:
USE [TestSSISProjects]
GO
DECLARE @RC int
DECLARE @folder_name sysname
DECLARE @environment_name nvarchar(128)
DECLARE @environment_description nvarchar(128)
DECLARE @project_name nvarchar(128)
DECLARE @ispacpath nvarchar(500)
DECLARE @SQLServerName nvarchar(100)
DECLARE @projectparam1 nvarchar(100)
-- Create a SSIS project in SSISFolder1
SET @folder_name = 'SSISFolder1'
SET @environment_name = 'DEV'
SET @environment_description = 'Development'
SET @project_name = 'TestSSISProject'
SET @ispacpath = 'D:\tmp\TestSSISProject\TestSSISProject\bin\Development\TestSSISProject.ispac'
SET @SQLServerName = NULL
SET @projectparam1 = 'SourceServer'
EXECUTE @RC = [dbo].[usp_CreateAndDeploySSISProject]
@folder_name
,@environment_name
,@environment_description
,@project_name
,@ispacpath
,@SQLServerName
,@projectparam1
GO
Executing this piece of code results in the following log from the stored procedure.
--------------------------------------------------------------------------------
Creating folder SSISFolder1
--------------------------------------------------------------------------------
[SSISDB].[catalog].[create_folder] N'SSISFolder1';
--------------------------------------------------------------------------------
Creating Environment DEV in the folder SSISFolder1
--------------------------------------------------------------------------------
[SSISDB].[catalog].[create_environment] N'SSISFolder1',N'DEV',N'Development';
--------------------------------------------------------------------------------
Deleting and adding Environmentvariables in DEV
--------------------------------------------------------------------------------
(4 row(s) affected)
EXECUTE [SSISDB].[catalog].[create_environment_variable] @folder_name = N'SSISFolder1', @environment_name = N'DEV', @variable_name = N'envMartConnectionString', @data_type = N'String', @sensitive = 0, @description = N'Connection string for Mart',
@value = N'Data Source=SQL2017CTP21;Initial Catalog=Mart;Provider=SQLNCLI11.1;Integrated Security=SSPI;Auto Translate=False;' ;
EXECUTE [SSISDB].[catalog].[create_environment_variable] @folder_name = N'SSISFolder1', @environment_name = N'DEV', @variable_name = N'envMetaConnectionString', @data_type = N'String', @sensitive = 0, @description = N'Connection string for Meta',
@value = N'Data Source=SQL2017CTP21;Initial Catalog=rMeta;Provider=SQLNCLI11.1;Integrated Security=SSPI;Auto Translate=False;' ;
EXECUTE [SSISDB].[catalog].[create_environment_variable] @folder_name = N'SSISFolder1', @environment_name = N'DEV', @variable_name = N'envStagingConnectionString', @data_type = N'String', @sensitive = 0, @description = N'Connection string for Staging',
@value = N'Data Source=SQL2017CTP21;Initial Catalog=Staging;Provider=SQLNCLI11.1;Integrated Security=SSPI;Auto Translate=False;' ;
EXECUTE [SSISDB].[catalog].[create_environment_variable] @folder_name = N'SSISFolder1', @environment_name = N'DEV', @variable_name = N'envSourceConnectionString', @data_type = N'String', @sensitive = 0, @description = N'Connection string for source',
@value = N'USER=<user> <Somevariables> <HOST>=SourceServer PASSWD=!@#@#!@%%$%' ;
--------------------------------------------------------------------------------
Deploy the ispac of the SSIS project in the SSIS Catalog
--------------------------------------------------------------------------------
SET @ispacBinary = (SELECT * FROM OPENROWSET(BULK N'D:\tmp\TestSSISProject\TestSSISProject\bin\Development\TestSSISProject.ispac', SINGLE_BLOB) as BinaryData)
EXECUTE [SSISDB].[catalog].[deploy_project] @folder_name = N'SSISFolder1', @project_name = N'TestSSISProject', @Project_Stream = @ispacBinary
--------------------------------------------------------------------------------
Creating an environmentreference in DEV
--------------------------------------------------------------------------------
EXECUTE [SSISDB].[catalog].[create_environment_reference] @environment_name = N'DEV', @reference_id = @reference_id , @project_name = N'TestSSISProject', @folder_name = N'SSISFolder1', @reference_type = N'R'
--------------------------------------------------------------------------------
Connect the environment variables with the project parameters
--------------------------------------------------------------------------------
(4 row(s) affected)
EXECUTE [SSISDB].[catalog].[set_object_parameter_value]@object_type = '20', @folder_name = N'SSISFolder1', @project_name = N'TestSSISProject', @parameter_name = N'MartConnectionString', @parameter_value = N'envMartConnectionString', @object_name = N'', @value_type = N'R';
EXECUTE [SSISDB].[catalog].[set_object_parameter_value]@object_type = '20', @folder_name = N'SSISFolder1', @project_name = N'TestSSISProject', @parameter_name = N'MetaConnectionString', @parameter_value = N'envMetaConnectionString', @object_name = N'', @value_type = N'R';
EXECUTE [SSISDB].[catalog].[set_object_parameter_value]@object_type = '20', @folder_name = N'SSISFolder1', @project_name = N'TestSSISProject', @parameter_name = N'StagingConnectionString', @parameter_value = N'envStagingConnectionString', @object_name = N'', @value_type = N'R';
EXECUTE [SSISDB].[catalog].[set_object_parameter_value]@object_type = '20', @folder_name = N'SSISFolder1', @project_name = N'TestSSISProject', @parameter_name = N'SourceConnectionString', @parameter_value = N'envSourceConnectionString', @object_name = N'', @value_type = N'R';
--------------------------------------------------------------------------------
Done
--------------------------------------------------------------------------------
And now a SSIS Catalog folder with a SSIS project and an environment is created
And now I can execute the SSIS package(s) with the environment variables of the SSISCatalog :
Adding a SSIS project to an existing folder
Now I can add another SSIS project in the SSIS Catalog folder. Let's try that. Don't forget to rename the project (in this testsituation) and execute a build.
Execute the following code:
DECLARE @RC int
DECLARE @folder_name sysname
DECLARE @environment_name nvarchar(128)
DECLARE @environment_description nvarchar(128)
DECLARE @project_name nvarchar(128)
DECLARE @ispacpath nvarchar(500)
DECLARE @SQLServerName nvarchar(100)
DECLARE @projectparam1 nvarchar(100)
SET @folder_name = 'SSISFolder1'
SET @environment_name = 'DEV'
SET @environment_description = 'Development'
SET @project_name = 'TestSSISProject2'
SET @ispacpath = 'D:\tmp\TestSSISProject\TestSSISProject\bin\Development\TestSSISProject2.ispac'
SET @SQLServerName = NULL
SET @projectparam1 = 'SourceServer'
EXECUTE @RC = [dbo].[usp_CreateAndDeploySSISProject]
@folder_name
,@environment_name
,@environment_description
,@project_name
,@ispacpath
,@SQLServerName
,@projectparam1
GO
Here is the logging again:
--------------------------------------------------------------------------------
Creating folder SSISFolder1
--------------------------------------------------------------------------------
Not able to (re) create folder SSISFolder1 because it already exists
--------------------------------------------------------------------------------
Creating Environment DEV in the folder SSISFolder1
--------------------------------------------------------------------------------
Not able to (re) create Environment DEV because it already exists in the folder SSISFolder1
--------------------------------------------------------------------------------
Deleting and adding Environmentvariables in DEV
--------------------------------------------------------------------------------
(4 row(s) affected)
EXECUTE [SSISDB].[catalog].[delete_environment_variable] @folder_name = N'SSISFolder1', @environment_name = N'DEV', @variable_name = N'envMartConnectionString'
EXECUTE [SSISDB].[catalog].[create_environment_variable] @folder_name = N'SSISFolder1', @environment_name = N'DEV', @variable_name = N'envMartConnectionString', @data_type = N'String', @sensitive = 0, @description = N'Connection string for Mart',
@value = N'Data Source=SQL2017CTP21;Initial Catalog=Mart;Provider=SQLNCLI11.1;Integrated Security=SSPI;Auto Translate=False;' ;
EXECUTE [SSISDB].[catalog].[delete_environment_variable] @folder_name = N'SSISFolder1', @environment_name = N'DEV', @variable_name = N'envMetaConnectionString'
EXECUTE [SSISDB].[catalog].[create_environment_variable] @folder_name = N'SSISFolder1', @environment_name = N'DEV', @variable_name = N'envMetaConnectionString', @data_type = N'String', @sensitive = 0, @description = N'Connection string for Meta',
@value = N'Data Source=SQL2017CTP21;Initial Catalog=rMeta;Provider=SQLNCLI11.1;Integrated Security=SSPI;Auto Translate=False;' ;
EXECUTE [SSISDB].[catalog].[delete_environment_variable] @folder_name = N'SSISFolder1', @environment_name = N'DEV', @variable_name = N'envStagingConnectionString'
EXECUTE [SSISDB].[catalog].[create_environment_variable] @folder_name = N'SSISFolder1', @environment_name = N'DEV', @variable_name = N'envStagingConnectionString', @data_type = N'String', @sensitive = 0, @description = N'Connection string for Staging',
@value = N'Data Source=SQL2017CTP21;Initial Catalog=Staging;Provider=SQLNCLI11.1;Integrated Security=SSPI;Auto Translate=False;' ;
EXECUTE [SSISDB].[catalog].[delete_environment_variable] @folder_name = N'SSISFolder1', @environment_name = N'DEV', @variable_name = N'envSourceConnectionString'
EXECUTE [SSISDB].[catalog].[create_environment_variable] @folder_name = N'SSISFolder1', @environment_name = N'DEV', @variable_name = N'envSourceConnectionString', @data_type = N'String', @sensitive = 0, @description = N'Connection string for source',
@value = N'USER=<user> <Somevariables> <HOST>=SourceServer PASSWD=!@#@#!@%%$%' ;
--------------------------------------------------------------------------------
Deploy the ispac of the SSIS project in the SSIS Catalog
--------------------------------------------------------------------------------
SET @ispacBinary = (SELECT * FROM OPENROWSET(BULK N'D:\tmp\TestSSISProject\TestSSISProject\bin\Development\TestSSISProject2.ispac', SINGLE_BLOB) as BinaryData)
EXECUTE [SSISDB].[catalog].[deploy_project] @folder_name = N'SSISFolder1', @project_name = N'TestSSISProject2', @Project_Stream = @ispacBinary
--------------------------------------------------------------------------------
Creating an environmentreference in DEV
--------------------------------------------------------------------------------
EXECUTE [SSISDB].[catalog].[create_environment_reference] @environment_name = N'DEV', @reference_id = @reference_id , @project_name = N'TestSSISProject2', @folder_name = N'SSISFolder1', @reference_type = N'R'
--------------------------------------------------------------------------------
Connect the environment variables with the project parameters
--------------------------------------------------------------------------------
(4 row(s) affected)
EXECUTE [SSISDB].[catalog].[set_object_parameter_value]@object_type = '20', @folder_name = N'SSISFolder1', @project_name = N'TestSSISProject2', @parameter_name = N'MartConnectionString', @parameter_value = N'envMartConnectionString', @object_name = N'', @value_type = N'R';
EXECUTE [SSISDB].[catalog].[set_object_parameter_value]@object_type = '20', @folder_name = N'SSISFolder1', @project_name = N'TestSSISProject2', @parameter_name = N'MetaConnectionString', @parameter_value = N'envMetaConnectionString', @object_name = N'', @value_type = N'R';
EXECUTE [SSISDB].[catalog].[set_object_parameter_value]@object_type = '20', @folder_name = N'SSISFolder1', @project_name = N'TestSSISProject2', @parameter_name = N'StagingConnectionString', @parameter_value = N'envStagingConnectionString', @object_name = N'', @value_type = N'R';
EXECUTE [SSISDB].[catalog].[set_object_parameter_value]@object_type = '20', @folder_name = N'SSISFolder1', @project_name = N'TestSSISProject2', @parameter_name = N'SourceConnectionString', @parameter_value = N'envSourceConnectionString', @object_name = N'', @value_type = N'R';
--------------------------------------------------------------------------------
Done
--------------------------------------------------------------------------------
And now a new project is placed besides the other project in the SISS folder in the SSIS catalog.
Adding an environment to an existing SSIS folder
It is also possible to add an extra enviroment in the SSIS folder in the SSIS catalog. In this case the same parameter values are used but off course it is a small step to make this flexible.
DECLARE @RC int
DECLARE @folder_name sysname
DECLARE @environment_name nvarchar(128)
DECLARE @environment_description nvarchar(128)
DECLARE @project_name nvarchar(128)
DECLARE @ispacpath nvarchar(500)
DECLARE @SQLServerName nvarchar(100)
DECLARE @projectparam1 nvarchar(100)
SET @folder_name = 'SSISFolder1'
SET @environment_name = 'TST'
SET @environment_description = 'Test'
SET @project_name = 'TestSSISProject2'
SET @ispacpath = 'D:\tmp\TestSSISProject\TestSSISProject\bin\Development\TestSSISProject2.ispac'
SET @SQLServerName = NULL
SET @projectparam1 = 'SourceServer'
EXECUTE @RC = [dbo].[usp_CreateAndDeploySSISProject]
@folder_name
,@environment_name
,@environment_description
,@project_name
,@ispacpath
,@SQLServerName
,@projectparam1
GO
Resulting in the following log information :
--------------------------------------------------------------------------------
Creating folder SSISFolder1
--------------------------------------------------------------------------------
Not able to (re) create folder SSISFolder1 because it already exists
--------------------------------------------------------------------------------
Creating Environment TST in the folder SSISFolder1
--------------------------------------------------------------------------------
[SSISDB].[catalog].[create_environment] N'SSISFolder1',N'TST',N'Test';
--------------------------------------------------------------------------------
Deleting and adding Environmentvariables in TST
--------------------------------------------------------------------------------
(4 row(s) affected)
EXECUTE [SSISDB].[catalog].[create_environment_variable] @folder_name = N'SSISFolder1', @environment_name = N'TST', @variable_name = N'envMartConnectionString', @data_type = N'String', @sensitive = 0, @description = N'Connection string for Mart',
@value = N'Data Source=SQL2017CTP21;Initial Catalog=Mart;Provider=SQLNCLI11.1;Integrated Security=SSPI;Auto Translate=False;' ;
EXECUTE [SSISDB].[catalog].[create_environment_variable] @folder_name = N'SSISFolder1', @environment_name = N'TST', @variable_name = N'envMetaConnectionString', @data_type = N'String', @sensitive = 0, @description = N'Connection string for Meta',
@value = N'Data Source=SQL2017CTP21;Initial Catalog=rMeta;Provider=SQLNCLI11.1;Integrated Security=SSPI;Auto Translate=False;' ;
EXECUTE [SSISDB].[catalog].[create_environment_variable] @folder_name = N'SSISFolder1', @environment_name = N'TST', @variable_name = N'envStagingConnectionString', @data_type = N'String', @sensitive = 0, @description = N'Connection string for Staging',
@value = N'Data Source=SQL2017CTP21;Initial Catalog=Staging;Provider=SQLNCLI11.1;Integrated Security=SSPI;Auto Translate=False;' ;
EXECUTE [SSISDB].[catalog].[create_environment_variable] @folder_name = N'SSISFolder1', @environment_name = N'TST', @variable_name = N'envSourceConnectionString', @data_type = N'String', @sensitive = 0, @description = N'Connection string for source',
@value = N'USER=<user> <Somevariables> <HOST>=SourceServer PASSWD=!@#@#!@%%$%' ;
--------------------------------------------------------------------------------
Deploy the ispac of the SSIS project in the SSIS Catalog
--------------------------------------------------------------------------------
SET @ispacBinary = (SELECT * FROM OPENROWSET(BULK N'D:\tmp\TestSSISProject\TestSSISProject\bin\Development\TestSSISProject2.ispac', SINGLE_BLOB) as BinaryData)
EXECUTE [SSISDB].[catalog].[deploy_project] @folder_name = N'SSISFolder1', @project_name = N'TestSSISProject2', @Project_Stream = @ispacBinary
--------------------------------------------------------------------------------
Creating an environmentreference in TST
--------------------------------------------------------------------------------
EXECUTE [SSISDB].[catalog].[create_environment_reference] @environment_name = N'TST', @reference_id = @reference_id , @project_name = N'TestSSISProject2', @folder_name = N'SSISFolder1', @reference_type = N'R'
--------------------------------------------------------------------------------
Connect the environment variables with the project parameters
--------------------------------------------------------------------------------
(4 row(s) affected)
EXECUTE [SSISDB].[catalog].[set_object_parameter_value]@object_type = '20', @folder_name = N'SSISFolder1', @project_name = N'TestSSISProject2', @parameter_name = N'MartConnectionString', @parameter_value = N'envMartConnectionString', @object_name = N'', @value_type = N'R';
EXECUTE [SSISDB].[catalog].[set_object_parameter_value]@object_type = '20', @folder_name = N'SSISFolder1', @project_name = N'TestSSISProject2', @parameter_name = N'MetaConnectionString', @parameter_value = N'envMetaConnectionString', @object_name = N'', @value_type = N'R';
EXECUTE [SSISDB].[catalog].[set_object_parameter_value]@object_type = '20', @folder_name = N'SSISFolder1', @project_name = N'TestSSISProject2', @parameter_name = N'StagingConnectionString', @parameter_value = N'envStagingConnectionString', @object_name = N'', @value_type = N'R';
EXECUTE [SSISDB].[catalog].[set_object_parameter_value]@object_type = '20', @folder_name = N'SSISFolder1', @project_name = N'TestSSISProject2', @parameter_name = N'SourceConnectionString', @parameter_value = N'envSourceConnectionString', @object_name = N'', @value_type = N'R';
--------------------------------------------------------------------------------
Done
--------------------------------------------------------------------------------
And this is how it looks like in the SSIS catalog :
Deleting a SSISfolder with all of its projects and environments (Blogpost for later)
Last step of this blogpost is to clean up the SSIS catalog. For this reason I've created another stored procedure : usp_DropSSISProject. This is not described in this blog post
USE [TestSSISProjects]
GO
DECLARE @RC int
DECLARE @parfolder_name sysname
DECLARE @parenvironment_name nvarchar(128)
DECLARE @parproject_name nvarchar(128)
DECLARE @pardeleteall nchar(1)
SET @parfolder_name = 'SSISFolder1'
SET @parenvironment_name = NULL
SET @parproject_name = NULL
SET @pardeleteall = 'Y'
EXECUTE @RC = [dbo].[usp_DropSSISProject]
@parfolder_name
,@parenvironment_name
,@parproject_name
,@pardeleteall
GO
And executing the code will result in the following log information:
--------------------------------------------------------------------------------
Delete all Project (s) in SSISFolder1
--------------------------------------------------------------------------------
(2 row(s) affected)
[SSISDB].[catalog].[delete_project] @folder_name = N'SSISFolder1', @project_name = N'TestSSISProject';
[SSISDB].[catalog].[delete_project] @folder_name = N'SSISFolder1', @project_name = N'TestSSISProject2';
--------------------------------------------------------------------------------
Delete Environment (s) in SSISFolder1
--------------------------------------------------------------------------------
(2 row(s) affected)
[SSISDB].[catalog].[delete_environment] @folder_name = N'SSISFolder1', @environment_name = N'DEV';
[SSISDB].[catalog].[delete_environment] @folder_name = N'SSISFolder1', @environment_name = N'TST';
--------------------------------------------------------------------------------
Deleting folder SSISFolder1
--------------------------------------------------------------------------------
[SSISDB].[catalog].[delete_folder] N'SSISFolder1';
The SSIS Catalog is empty now
Conclusion
This blogpsot describes the implementation of a stored procedure that creates folders, projects, environments and environment variables in an almost automated way.
Hennie
