Copy or Migrate a SSISDB environment 2.0

Based on the blog “Copy or Migrate a SSISDB environment” of my colleague Christophe, I create a new one named 2.0.

In the precedent script, you need to give the folder name and environment name and he generates all environment variables for these 2 parameters.
In my case, I need to generate all folders and what are in these folders.

The 2.0 script will generate the creation of the folder and the environment name if they not exist in add of all environment variables.
It is a little bit more global! 😛

To create a Folder, I need to generate this type of script:

DECLARE @FolderName nvarchar(128) = NULL; 
SET @FolderName = 'MyFolfer';
IF ((SELECT count(*) from [SSISDB].[catalog].folders where name=@FolderName)=0) 
BEGIN
       EXEC [SSISDB].[catalog].[create_folder] @folder_name=@FolderName; 
END;

To create the environment name, I need to generate this type of script:

 
DECLARE @EnvName nvarchar(128)= NULL;
SET @EnvName = 'DEV_ENV';
IF ((SELECT count(*) from [SSISDB].[catalog].environments where name=@EnvName)=0)
BEGIN
	EXEC [SSISDB].[catalog].[create_environment] @folder_name=@FolderName, @environment_name=@EnvName, @environment_description=N'';

END;

After, we generate like Christophe does,  environment variables:

EXEC [SSISDB].[catalog].[create_environment_variable] @folder_name=@FolderName, @environment_name=@EnvName, @variable_name=N'myVariable',@data_type=N'String',@sensitive=0, @value = N'\\myshare\',@description=N'';

To create all scripts, I will just insert in the migration script both above scripts in my SELECT:

 
SELECT 'DECLARE @FolderName nvarchar(128) = NULL; DECLARE @EnvName nvarchar(128)= NULL;'
SELECT 
'SET @EnvName = ''' + e.name + ''';' + 
'SET @FolderName = ''' + f.name + ''';'+
'IF ((SELECT count(*) from [SSISDB].[catalog].folders where name=@FolderName)=0) 
BEGIN
       EXEC [SSISDB].[catalog].[create_folder] @folder_name=@FolderName; 
END;'
+
'IF ((SELECT count(*) from [SSISDB].[catalog].environments where name=@EnvName)=0)
BEGIN
EXEC [SSISDB].[catalog].[create_environment] @folder_name=@FolderName, @environment_name=@EnvName, @environment_description=N''' + COALESCE(e.description, '') + '''' +'; END;'
+' EXEC [SSISDB].[catalog].[create_environment_variable] 
@folder_name=@FolderName, 
@environment_name=@EnvName, 
@variable_name=N'''+ ev.name + ''', 
@data_type=N'''+ ev.type + ''', 
@sensitive='+ CONVERT(NCHAR,ev.sensitive) +', 
@value = ' + 
CASE ev.sensitive
WHEN 0 THEN 
CASE ev.type 
WHEN 'Date Time' THEN ''''+ CONVERT(NVARCHAR(max),ev.value) + ''''
WHEN 'String' THEN 'N'''+ CONVERT(NVARCHAR(max),ev.value) + ''''
ELSE CONVERT(NVARCHAR(max),ev.value)
END 
WHEN 1 THEN 
'##########'
END + ',
@description=N'''+ ev.description + ''';'
as tsql_EnvVarcreate
FROM SSISDB.catalog.folders f
INNER JOIN SSISDB.catalog.environments e on e.folder_id = f.folder_id
INNER JOIN SSISDB.catalog.environment_variables ev on ev.environment_id = e.environment_id

The first Select is just to declare the 2 variables @FolderName and @EnvName
The second select will create all our environment variables with the folder and environment name.
Here below a screenshot of the result of the script, when I run the script

As you can see, for all environment variables, this script will have the “exist” test and creation of the folder and environment name.
Copy the result of the script (column tsql_EnvCopy) in a copy it in a SSMS Query window connected to the server where you want to deploy your environment
Be careful and verify before running the result script that you have the good name for all folders, environment name and variable name
Last step is just to run the script:

Et voila, I do my first SSIS Catalog environment migration successfully!

Thank you Christophe for showing the way with your first blog 😎