SQL : Deploying a SSIS project with a stored procedure

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