Just another WordPress.com site

SSIS 2012 – easily copy environment variables to new servers or new environments

Run below script on the source DBserver, it will generate the script for generating environment variables. Change the value for the parameters created in the beginning:

 SET NOCOUNT ON 
DECLARE
    @folder_name nvarchar(200)              = 'ProjectFolderName',
    @environment_name_current nvarchar(200) = 'DEV',
    @environment_name_new nvarchar(200)     = 'TEST',
    @name sysname,
    @sensitive bit,
    @description nvarchar(1024),
    @value sql_variant,
    @type nvarchar(128)
PRINT 'DECLARE
    @folder_id bigint,
    @environment_id bigint'
PRINT ''
--> Create folder if it doesn't exist and get folder_id
PRINT 'IF NOT EXISTS (SELECT 1 FROM [SSISDB].[catalog].[folders] WHERE name = N''' + @folder_name + ''')
    EXEC [SSISDB].[catalog].[create_folder] @folder_name=N''' + @folder_name + ''', @folder_id=@folder_id OUTPUT
ELSE
    SET @folder_id = (SELECT folder_id FROM [SSISDB].[catalog].[folders] WHERE name = N''' + @folder_name + ''')'
PRINT '' 
--> Create environment if it doesn't exist
PRINT 'IF NOT EXISTS (SELECT 1 FROM [SSISDB].[catalog].[environments] WHERE folder_id = @folder_id AND name = N''' + @environment_name_new + ''')
    EXEC [SSISDB].[catalog].[create_environment] @environment_name=N''' + @environment_name_new + ''', @folder_name=N''' + @folder_name + ''''
PRINT '' 
--> Get the environment_id
PRINT 'SET @environment_id = (SELECT environment_id FROM [SSISDB].[catalog].[environments] WHERE folder_id = @folder_id and name = N''' + @environment_name_new + ''')'
PRINT ''
--> Making cursor because mapping of sql_variant datatype is different than the normal datatypes
DECLARE cur CURSOR FOR
    SELECT c.name, c.sensitive, c.description, c.value, c.type
    FROM [SSISDB].[catalog].[folders] a
        INNER JOIN [SSISDB].[catalog].[environments] b
            ON a.folder_id =  b.folder_id
        INNER JOIN [SSISDB].[catalog].[environment_variables] c
           ON b.environment_id = c.environment_id
    WHERE a.name = @folder_name
        AND b.name = @environment_name_current
OPEN cur
FETCH NEXT FROM cur INTO @name, @sensitive, @description, @value, @type
PRINT 'DECLARE @var sql_variant'
PRINT ''
WHILE (@@FETCH_STATUS = 0)
    BEGIN
        PRINT 'SET @var = N''' + CONVERT(nvarchar(max), @value) + ''''
        PRINT 'IF NOT EXISTS (SELECT 1 FROM [SSISDB].[catalog].[environment_variables] WHERE environment_id = @environment_id AND name = N''' + @name + ''')
            EXEC [SSISDB].[catalog].[create_environment_variable] @variable_name=N''' + @name + ''', @sensitive=' + CONVERT(varchar(2), @sensitive) + ', @description=N''' + @description + ''', @environment_name=N''' + @environment_name_new + ''', @folder_name=N''' + @folder_name + ''', @value=@var, @data_type=N''' + @type + ''''
        PRINT ''
    FETCH NEXT FROM cur INTO @name, @sensitive, @description, @value, @type
    END
CLOSE cur
DEALLOCATE cur

Copy and paste the generated script on the target DBserver, and execute, after it is done, validate the variable list by comparing with the source variable list. (Some variables may be missed out if there’s error raised during the execution.)

Reference: Source of the script

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s