SSIS 2012 – create environment variables from project parameters

Posted: 2013.05.28 in 2012

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
Comments
  1. […] SSIS 2012 – create environment variables from project parameters […]

    • Bruce says:

      Henning, thanks for the script, great help. I updated a few things based on what I needed. I added a @project_name parameter…if there isn’t a @project_name filter then the script will create entries for package level parameters. I added an IF statement (not pretty but I was in a hurry) where the script sets @value. It was failing on Int32. I just took care of my needs, but the script should be updated to account for all possible datatypes. Wrapping non-string data with the apostrophes causes the EXEC [SSISDB].[catalog].[create_environment_variable] proc to choke. Here’s is the script after I updated in case you want to account for these issues in the master. Thanks again for publishing!

      /*
      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, http://www.thefirstsql.com, 2013-05-28
      */

      DECLARE
      @folder_name nvarchar(200) = ‘ProjectFolderName’,
      @project_name nvarchar(128) = ‘ProjectName’,
      @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 a.[object_name] = @project_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’

      IF @data_type = ‘Int32′
      BEGIN
      PRINT ‘ SET @value = ‘ + CONVERT(nvarchar(max),@design_default_value)
      END
      ELSE
      BEGIN
      PRINT ‘ SET @value = N”’ + CONVERT(nvarchar(max), @design_default_value) + ””
      END

      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

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