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


Following my investigations on SSIS 2012 and the issues mentioned in my previous blog post, create environment variables from project parameters, I created a script that easily lets you copy all the existing environment variables either to a new environment on the same server or to a new (or existing) environment on a completely different server.

Please note: this script will NOT make any changes to your environments by itself! It merely generates a sql script that has to be copy/pasted in to a SSMS query window and executed manually.

/*
This script will take all environment variables in an existing environment and make "insert" scripts 
out of them so you can easily deploy them to a new server or a new environment on the same server.

Script created by Henning Frettem, www.thefirstsql.com, 2013-05-28
*/

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
Advertisements

13 thoughts on “SSIS 2012 – easily copy environment variables to new servers or new environments

  1. This script saved me time, thanks! One remark though: I had two variables of type boolean that were scripted as SET @var = N’0′ but this raised an error about the data type. Manually editing to SET @var = 0 solved this.

    • Thanks for your comment Frenk, I realize that my testing was limited to connection strings only and that the script needs to be tested for the other data types as well. Thanks for pointing that out! I’ll try to fix it as soon as possible :)

  2. Nice script! Thank you! You might want to add copying descriptions for both folder and environment. Also when variable is sensitive, the returned value is NULL, and thus entire SET @Var line of code disappears which causes an error when variable is created. The generated script has to be manually edited to insert SET @Var =something for sensitive variables.
    It would also be great to add code to copy mapping of Project parameters to environment variables.
    It is an important component of environment variables that needs to be copied along with variables.

  3. Pingback: SSIS 2012 – easily copy environment variables to new servers or new environments | Vista SQL Team

  4. Newbe here, The script seems to be working for most people, but after the
    PRINT ‘DECLARE @var sql_variant’
    my script ends. I have 2 variables, but those 2 don’t seem to be noticed in the while loop…?

  5. Great Script, only thing to remember is if any of the variable values is NULL, script wouldn’t be generated as expected. for e.g. description was null and it didn’t print the create env variable statement.

  6. Pingback: Accessing Environment Variables In Esql – Dolove.info

  7. I changed a little bit so that it could take care of non-string variables value settings.

    IF @type IN (N’String’, N’Date Time’)
    PRINT ‘SET @var = N”’ + CONVERT(nvarchar(max), @value) + ””
    ELSE
    PRINT ‘SET @var = ‘ + CONVERT(nvarchar(max), @value)

  8. I have used this script many times. It is great! But I’m curious – Do you know of an easy way to script out and copy the mappings of projects to environment variables? I have dozens of projects, each with a dozen or more mappings to environment variables. These projects need to be migrated to a new server. I would like to avoid the tedious task of remapping all of the variables if possible. Thanks!

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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s