dbi Blog

Integration catalog is easy to work with and the environments feature is as well very convenient.

In SQL Server management Studio, there are a lot of features available to administrate your SQL Server instances, but some are missing that can be very useful. You have the possibility to move an SSISDB environment but this is only possible in another folder of your SSIS catalog. But how if you will to duplicate your environment or move it on another server.

With the below steps, you will be able to generate the script, to create your environment, on the same server changing the folder or/and the environment name or to run it on another server.

Run the script below where you have initially created your SSIS catalog environment

DECLARE @FolderName nvarchar(128) = N'SSISDemo'
DECLARE @EnvName nvarchar(128)= 'Env_SSISDemo1'
SELECT 
'DECLARE @FolderName nvarchar(128) = N''' + @FolderName + '''
DECLARE @EnvName nvarchar(128)= ''' + @EnvName + '''
EXEC [SSISDB].[catalog].[create_environment] @folder_name=@FolderName, @environment_name=@EnvName, @environment_description=N''' + COALESCE(e.description, '') + '''' 
as tsql_EnvCopy 
FROM SSISDB.catalog.folders f
INNER JOIN SSISDB.catalog.environments e on e.folder_id = f.folder_id
WHERE f.name = @FolderName
AND e.name = @EnvName
UNION ALL 
SELECT 
'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
WHERE f.name = @FolderName
AND e.name = @EnvName

Copy the result of the script (column tsql_EnvCopy) in a copy it in a SSMS Query window connected to the server where your want to deploy your environment.
Adapt the script if needed.

On the server where you want to create the new environment, be sure that:
- The folder with the defined FolderName is existing.
- FolderName/EnvName combination is not already existing on the server.
- Change the values of the @FolderName or/and @EnvName if needed.

Change the values of the sensitive parameters:

As you can imagine the parameter values cannot be retrieved when they are set as sensitive. If you have such parameter replace the values ########## with the real ones before running the script.