You have SSIS projects deployed in the SSISDB catalog. You are using environments and environment variables for passing parameter values to SSIS Executables. This is a very common scenario.
You come to a situation that you need transfer the SSIS projects, environments and varaibles among different SSIS environments (DEV/TEST/QA/PROD). You already have bunch of environment variables defined in one of your environment and you need to transfer those to other environment.
Unfortunately there is no easy way to clone, copy or script the varaibles to transfer them to different environment. If you do not script them during creation in the SSMS, there is no option in SSMS to script them out. If you do not want to type all the varaibles manually again, you have to script those in some way.
Anyway the proper scripting or clonning is not completely trivial in case sensitive varaible values are being used as SSISDB uses multiple symmetric keys and certificates for the encryption and each Environment in the SSISDB has its own key and corresponding certificate. When cloning such sensitive value, the value needs to be decrypted with the source environment symmetric key and re-encrypted by the destination environment symmetric key.
sp_SSISCloneEnvironment
sp_SSISCloneEnvironment is a custom stored procedure which takes care about cloning the SSSI server variables among different environments in the same SSISDB catalog as well as it allows generate a script, which can be re-applied on even oon other SSIS Instance.
The stored procedure takes care about sensitive values re-encryption by proper certificate when clonning. As mentioned above, SSIS generates a separate symmetric key and certificate for each environment. To ensure proper future functionality, the stored procedure takes care about decryption of the sensitive variables when reading from current environment and re-encryption by proper destination environment symmetric key.
The stored procedure executes in the context of the AllSchemaOwner database user in the SSISDB, which ensure access to all areas of SSISDB including the needed certificates and symmetric keys. Only in areas where it needs to create a folder or environment it switches the context to the caller of the stored procedure as the procedures which are taking care abut the folder and environment creation must be executed under windows credentials.
The create scripts grants EXECUTE permission to the sssis_admin database role in the SSISDB catalog so the SSSIS admins can utilize that stored procedure.
Be carefull, anyone with EXECUTE permission on the stored procedure is capable of seeing decrypted sensitive values when printing the script. The script by default grants that permission to ssis_admin database role. If you want to keep that functionality only to sysadmins, remove the GRANT at the end of the script.
The complete source code for the sp_SSISCloneEnvironment is available in my SQL-Scripts project on GitHub in the SSISDB folder.
The syntax of the stored procedure is as follow:
sp_SSISCloneEnvironment
@sourceFolder = 'Source Folder Name'
,@sourceEnvironment = 'Source Environment Name'
,@destinationFolder = 'Destination Folder Name'
,@destinationEnvironment = 'Destination Environment Name'
,@autoCreate = 1 --Auto Create Destination
,@printScript = 1 --Specifies that Create Script should be output instead of actual execution
,@decryptSensitiveInScript = 1 --Specifies that the sensitive values should be decrypted in the create script
Currently when cloning the environment variables the destination environment must be empty if script is not being generated and the procedure is running in the non-interactive mode.
The generated script is also easily reusable as there are two variables in the beginning for specification of the destination folder and environment. By simple modification of those variables the script will generate the variables different environments.
Parameters
The procedure has several parameters where some are required and some optional
The core parameters are @sourceFolder, @sourceEnvironment and @destinationFolder. The @destinationEnvornment, @autoCreate, @printScript and @decryptSensistiveInScript are optional.
@source Folder
Specifies the source folder where the source environment is located
@sourceEnvironment
Specifies the name of the source environment from within source folder to be scripted
@destinationFolder
Name of the destination folder to wich the environment varaibles should be clonned.
@destinationEnvironment
Name of the destination environment to which the varaibles should be conned. This parameter is optional and if not provided, then the name of the source environment is used.
@autoCreate
Specifies whether the destination foolde and destination environment should be automatically crated if they do not exist. If the script is being generated then a statement which checks existence of the folder and environment are included in the script and missing folder and/or environment is automatically created if needed by the script.
Default value is 1 = true
@printScript
Specifies whether a script should be printed into the output window. If yes, then you simply copy the script from the output window and you can apply it immediatelly on other SSIS Instance.
Default value = 0, this means that the script executes within the SSISDB catalog and automatically clnes the variables
@decryptSensitiveInScript
Specifeis whether sensitive values should be decrypted in the script.If yes, then the values are part of the script in plain-text form, howver they are marked sensitive and will be encrypted after applying the script.
If the parameter is 0 (Default), then the sensitive values are not scripted out and instead NULL value is being scripted out. User must then fill those missing values priorexecution of the script.
Sample Output
Below is sample script generated by the stored procedure when @printScript=1 and @decryptSensitiveInScript=1
RAISERROR(N'sp_SSISCloneEnvironment v0.20 (2016-10-11) (C) 2016 Pavel Pawlowski', 0, 0) WITH NOWAIT;
RAISERROR(N'===================================================================', 0, 0) WITH NOWAIT;
DECLARE @destinationFolder nvarchar(128) = N'TEST' --Specify Destination Folder Name
DECLARE @destinationEnvironment nvarchar(128) = N'ClonedEnvironment' --Specify Destination Environment Name
--Checking for destination folder existence
IF NOT EXISTS(SELECT 1 FROM [SSISDB].[catalog].[folders] WHERE [name] = @destinationFolder)
BEGIN
RAISERROR(N'Creating missing Folder [%s]', 0, 0, @destinationFolder) WITH NOWAIT;
EXEC [SSISDB].[catalog].[create_folder] @folder_name = @destinationFolder
END
--Checking for destination environment existence
IF NOT EXISTS(
SELECT 1
FROM [SSISDB].[catalog].[environments] e
INNER JOIN [SSISDB].[catalog].[folders] f ON f.folder_id = e.folder_id
WHERE f.[name] = @destinationFolder AND e.[name] = @destinationEnvironment
)
BEGIN
RAISERROR(N'Creating missing Environment [%s]\[%s]', 0, 0, @destinationFolder, @destinationEnvironment) WITH NOWAIT
EXEC [SSISDB].[catalog].[create_environment] @folder_name = @destinationFolder, @environment_name = @destinationEnvironment, @environment_description = N''
END
--Checking for variables existence in destination eivnironment
IF EXISTS (
SELECT 1
FROM [SSISDB].[catalog].[environment_variables] ev
INNER JOIN [SSISDB].[catalog].[environments] e ON e.environment_id = ev.environment_id
INNER JOIN [SSISDB].[catalog].[folders] f ON f.folder_id = e.folder_id
WHERE
f.name = @destinationFolder AND e.name = @destinationEnvironment
)
BEGIN
RAISERROR(N'Destination Environment [%s]\[%s] is not empty. Clear all variables prior clonning environment.', 15, 2, @destinationFolder, @destinationEnvironment) WITH NOWAIT;
RETURN;
END
--Environment variables creation
DECLARE @var sql_variant
RAISERROR(N'Creating variable [SSISDB]\[%s]\[%s]\[V1]', 0, 0, @destinationFolder, @destinationEnvironment) WITH NOWAIT;
SET @var = CONVERT(bit, N'1');
EXEC [SSISDB].[catalog].[create_environment_variable] @variable_name=N'V1', @data_type=N'Boolean', @sensitive=False, @folder_name=@destinationFolder, @environment_name=@destinationEnvironment, @value=@var, @description=N''
RAISERROR(N'Creating variable [SSISDB]\[%s]\[%s]\[V10]', 0, 0, @destinationFolder, @destinationEnvironment) WITH NOWAIT;
SET @var = CONVERT(sql_variant, N'This is string parameters');
EXEC [SSISDB].[catalog].[create_environment_variable] @variable_name=N'V10', @data_type=N'String', @sensitive=False, @folder_name=@destinationFolder, @environment_name=@destinationEnvironment, @value=@var, @description=N''
RAISERROR(N'Creating variable [SSISDB]\[%s]\[%s]\[V11]', 0, 0, @destinationFolder, @destinationEnvironment) WITH NOWAIT;
SET @var = CONVERT(sql_variant, N'This is sensitive string'); --SENSITIVE
EXEC [SSISDB].[catalog].[create_environment_variable] @variable_name=N'V11', @data_type=N'String', @sensitive=True, @folder_name=@destinationFolder, @environment_name=@destinationEnvironment, @value=@var, @description=N''
RAISERROR(N'Creating variable [SSISDB]\[%s]\[%s]\[V12]', 0, 0, @destinationFolder, @destinationEnvironment) WITH NOWAIT;
SET @var = CONVERT(datetime, N'2010-12-31T00:00:00'); --SENSITIVE
EXEC [SSISDB].[catalog].[create_environment_variable] @variable_name=N'V12', @data_type=N'DateTime', @sensitive=True, @folder_name=@destinationFolder, @environment_name=@destinationEnvironment, @value=@var, @description=N''
RAISERROR(N'Creating variable [SSISDB]\[%s]\[%s]\[V13]', 0, 0, @destinationFolder, @destinationEnvironment) WITH NOWAIT;
SET @var = CONVERT(decimal(28, 18), N'99.990000000000000000'); --SENSITIVE
EXEC [SSISDB].[catalog].[create_environment_variable] @variable_name=N'V13', @data_type=N'Decimal', @sensitive=True, @folder_name=@destinationFolder, @environment_name=@destinationEnvironment, @value=@var, @description=N''
RAISERROR(N'Creating variable [SSISDB]\[%s]\[%s]\[V14]', 0, 0, @destinationFolder, @destinationEnvironment) WITH NOWAIT;
SET @var = CONVERT(int, N'56'); --SENSITIVE
EXEC [SSISDB].[catalog].[create_environment_variable] @variable_name=N'V14', @data_type=N'Int32', @sensitive=True, @folder_name=@destinationFolder, @environment_name=@destinationEnvironment, @value=@var, @description=N''
RAISERROR(N'Creating variable [SSISDB]\[%s]\[%s]\[V2]', 0, 0, @destinationFolder, @destinationEnvironment) WITH NOWAIT;
SET @var = CONVERT(tinyint, N'5');
EXEC [SSISDB].[catalog].[create_environment_variable] @variable_name=N'V2', @data_type=N'Byte', @sensitive=False, @folder_name=@destinationFolder, @environment_name=@destinationEnvironment, @value=@var, @description=N''
RAISERROR(N'Creating variable [SSISDB]\[%s]\[%s]\[V3]', 0, 0, @destinationFolder, @destinationEnvironment) WITH NOWAIT;
SET @var = CONVERT(datetime, N'2016-01-01T00:00:00');
EXEC [SSISDB].[catalog].[create_environment_variable] @variable_name=N'V3', @data_type=N'DateTime', @sensitive=False, @folder_name=@destinationFolder, @environment_name=@destinationEnvironment, @value=@var, @description=N''
RAISERROR(N'Creating variable [SSISDB]\[%s]\[%s]\[V4]', 0, 0, @destinationFolder, @destinationEnvironment) WITH NOWAIT;
SET @var = CONVERT(decimal(28, 18), N'12.390000000000000000');
EXEC [SSISDB].[catalog].[create_environment_variable] @variable_name=N'V4', @data_type=N'Decimal', @sensitive=False, @folder_name=@destinationFolder, @environment_name=@destinationEnvironment, @value=@var, @description=N''
RAISERROR(N'Creating variable [SSISDB]\[%s]\[%s]\[V5]', 0, 0, @destinationFolder, @destinationEnvironment) WITH NOWAIT;
SET @var = CONVERT(float, N'156.987');
EXEC [SSISDB].[catalog].[create_environment_variable] @variable_name=N'V5', @data_type=N'Double', @sensitive=False, @folder_name=@destinationFolder, @environment_name=@destinationEnvironment, @value=@var, @description=N''
RAISERROR(N'Creating variable [SSISDB]\[%s]\[%s]\[V6]', 0, 0, @destinationFolder, @destinationEnvironment) WITH NOWAIT;
SET @var = CONVERT(int, N'123456');
EXEC [SSISDB].[catalog].[create_environment_variable] @variable_name=N'V6', @data_type=N'Int32', @sensitive=False, @folder_name=@destinationFolder, @environment_name=@destinationEnvironment, @value=@var, @description=N''
RAISERROR(N'Creating variable [SSISDB]\[%s]\[%s]\[V7]', 0, 0, @destinationFolder, @destinationEnvironment) WITH NOWAIT;
SET @var = CONVERT(bigint, N'987654321');
EXEC [SSISDB].[catalog].[create_environment_variable] @variable_name=N'V7', @data_type=N'Int64', @sensitive=False, @folder_name=@destinationFolder, @environment_name=@destinationEnvironment, @value=@var, @description=N''
RAISERROR(N'Creating variable [SSISDB]\[%s]\[%s]\[V8]', 0, 0, @destinationFolder, @destinationEnvironment) WITH NOWAIT;
SET @var = CONVERT(smallint, N'9');
EXEC [SSISDB].[catalog].[create_environment_variable] @variable_name=N'V8', @data_type=N'SByte', @sensitive=False, @folder_name=@destinationFolder, @environment_name=@destinationEnvironment, @value=@var, @description=N''
RAISERROR(N'Creating variable [SSISDB]\[%s]\[%s]\[V9]', 0, 0, @destinationFolder, @destinationEnvironment) WITH NOWAIT;
SET @var = CONVERT(float, N'56');
EXEC [SSISDB].[catalog].[create_environment_variable] @variable_name=N'V9', @data_type=N'Single', @sensitive=False, @folder_name=@destinationFolder, @environment_name=@destinationEnvironment, @value=@var, @description=N''
Pavel's SQL Pages 