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