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

SSIS 2012 – create environment variables from project parameters


A little late in the game perhaps but I just recently started looking in to SQL Server 2012 and especially SSIS, and there were some shortcomings (in my opinion) in regards to the configurations. The new deployment model with versioning and everything is utterly fantastic (!!), and making project parameters is easy peasy. However, when it comes to making the project parameters configurable across different environments it’s a different story. Assuming you already know the deal about the SSISDB catalog and the environments, I feel that the actual creation of the environment variables is very cumbersome. They are usually a very close replica of the design time project parameters, so to make the creation of the variables easier on myself I created a script that takes all the project parameters of a deployed project (deployed to your dev machine is sufficient), check if an environment variable with the same name exists and if it doesn’t then create it with the design time value.

Important: changes to the project parameters are only detected once the project is deployed to the Integration Services catalog!

/*
This script will take all project parameters with their design time values in your SSIS-project and create
an "insert script" for making them in to SSIS environment variables. It will NOT make any changes to
your databases!
  
Script created by Henning Frettem, www.thefirstsql.com, 2013-05-28
*/
  
DECLARE
    @folder_name nvarchar(200)      = 'ProjectFolderName',
    @environment_name nvarchar(200) = 'DEV',
    @parameter_name varchar(200),
    @design_default_value sql_variant,
    @sensitive bit, 
    @description nvarchar(1024), 
    @data_type nvarchar(128)
 
--> Checking that the folder exists
IF NOT EXISTS (SELECT 1 FROM [SSISDB].[catalog].[folders] WHERE name = @folder_name)
    BEGIN
        RAISERROR('Provided folder name does not exist in the Integration Services catalog. Create the folder first, then run the script again', 16, 1)
        RETURN
    END
 
PRINT 'DECLARE 
    @folder_id bigint,
    @value sql_variant,
	@folder_name nvarchar(200)      = ''' + @folder_name + ''',
	@environment_name nvarchar(200) = ''' + @environment_name + ''''
 
PRINT ''
 
PRINT '--> Need folder_id for ''' + @folder_name + ''' 
    SET @folder_id = (SELECT folder_id FROM [SSISDB].[catalog].[folders] WHERE name = @folder_name)'
  
PRINT ''
 
PRINT '--> Create ' + @environment_name + ' environment in ''' + @folder_name + '''-folder if it does not exist
    IF NOT EXISTS (SELECT 1 FROM [SSISDB].[catalog].[environments] WHERE folder_id = @folder_id AND name = @environment_name)
		BEGIN
			EXEC [SSISDB].[catalog].[create_environment] @environment_name = @environment_name, @folder_name = @folder_name
			PRINT ''CREATED: Environment ' + @environment_name + ' in folder ' + @folder_name + '''
		END
	ELSE
		PRINT ''EXISTS: Environment ' + @environment_name + ' exists'''
 
PRINT ''
  
--> Create cursor for all project parameters
DECLARE cur CURSOR FOR
    SELECT a.parameter_name, a.design_default_value, a.sensitive, a.description, a.data_type
    FROM [SSISDB].[catalog].[object_parameters] a
        INNER JOIN [SSISDB].[catalog].[projects] b
            ON a.project_id = b.project_id
        INNER JOIN [SSISDB].[catalog].[folders] c
            ON b.folder_id = c.folder_id
    WHERE c.name = @folder_name
        AND SUBSTRING(a.parameter_name, 1, 3) <> 'CM.'
    ORDER BY a.parameter_name
  
OPEN cur
FETCH NEXT FROM cur INTO @parameter_name, @design_default_value, @sensitive, @description, @data_type
  
WHILE (@@FETCH_STATUS = 0)
    BEGIN
        PRINT '--> Creating variable ''' + @parameter_name + ''' if it doesn''t exist'
        PRINT ' SET @value = N''' + CONVERT(nvarchar(max), @design_default_value) + ''''
        PRINT '
            IF NOT EXISTS (SELECT 1 
                            FROM [SSISDB].[catalog].[environments] b 
                                INNER JOIN [SSISDB].[catalog].[environment_variables] c
                                    ON b.environment_id = c.environment_id
                            WHERE b.folder_id = @folder_id 
                                AND b.name = @environment_name
                                AND c.name = ''' + @parameter_name + ''')
				BEGIN
					EXEC [SSISDB].[catalog].[create_environment_variable] 
						@variable_name = ''' + @parameter_name + ''', 
						@sensitive = ' + CONVERT(varchar(2), @sensitive) + ', 
						@description = ''' + @description + ''', 
						@environment_name = @environment_name, 
						@folder_name = @folder_name, 
						@value = @value, 
						@data_type = ''' + @data_type + '''
					PRINT ''CREATED: Environment-variable ' + @parameter_name + '''
				END
			ELSE
				PRINT ''EXISTS: Environment-variable ' + @parameter_name + ''''

		PRINT ''
  
    FETCH NEXT FROM cur INTO @parameter_name, @design_default_value, @sensitive, @description, @data_type
    END
  
CLOSE cur
DEALLOCATE cur