SSIS 2012 Copy or Duplicate Environments

In SSIS 2012 there is a great new feature called environments. They can be thought of as a collection of parameters or variables. One of the questions I get asked when teaching SSIS 2012 is “Can I duplicate an environment?”. There is a move feature already included. But this moves the environment instead of copying.

image

Now you can write T-SQL Scripts like the one at the bottom of this blog to create an environment and create the variables in the environment, but this is time consuming. You can script this out when you first create the environment and the variables. But if you have an already existing environment then this is not possible.

So how do you copy an environments? Here is a little trick to use.

First, to create the new Environment, right click on the new folder and select create environment. This is the easy part. The hard part is getting all of the variables inserted. You may have 50 variables in the previous environment to copy over. We need an easy way to copy them all over to the new environment.

Next, right click on the new environment and select properties. Take note of the environment identifier. Do the same for the previous environment.

image

Next, go to the SSISDB and take a look at the internal.environment_references table and find the Environment ID’s. The Environment identifier comes from internal.environment_references table. Then go to the [SSISDB].[internal].[environment_variables] table. This table contains all of the variables in your environments. You will find the rows for the previous environment for each variable in that environment. Look for the previous environment id you just noted.

image

Now we need to write a T-SQL statement to duplicate these rows with the new environment ID. This is a simple Insert into statement followed by a select statement. You will need to add in the Select statement a hard coded value of the new environment ID in the columns and a where clause looking for the previous environment id.

INSERT INTO [internal].[environment_variables]
([environment_id]
,[name]
,[description]
,[type]
,[sensitive]
,[value]
,[sensitive_value]
,[base_data_type])
SELECT  10 as environment_id  –New Environment ID
,[name]
,[description]
,[type]
,[sensitive]
,[value]
,[sensitive_value]
,[base_data_type]
FROM [SSISDB].[internal].[environment_variables]
where environment_id = 9  –Previous Environment ID

Make sure you create the new environment first and get the id’s correct in this script and you should be all set.

Also…

Here is the T-SQL code that can be scripted out when you first create an environment and the variables in it. But this can only be done when you first create them. The above solution works on any existing environments.

EXEC [SSISDB].[catalog].[create_environment] @environment_name=N’Test’, @environment_description=N”, @folder_name=N’SSISDemo’

GO

DECLARE @var sql_variant = N’test’
EXEC [SSISDB].[catalog].[create_environment_variable] @variable_name=N’test’, @sensitive=False, @description=N”, @environment_name=N’Test’, @folder_name=N’SSISDemo’, @value=@var, @data_type=N’String’
GO

DECLARE @var sql_variant = N’test1′
EXEC [SSISDB].[catalog].[create_environment_variable] @variable_name=N’test1′, @sensitive=False, @description=N”, @environment_name=N’Test’, @folder_name=N’SSISDemo’, @value=@var, @data_type=N’String’
GO

DECLARE @var sql_variant = N’test3′
EXEC [SSISDB].[catalog].[create_environment_variable] @variable_name=N’test3′, @sensitive=False, @description=N”, @environment_name=N’Test’, @folder_name=N’SSISDemo’, @value=@var, @data_type=N’String’
GO

About these ads
This entry was posted in SSIS, Syndication and tagged , . Bookmark the permalink.

8 Responses to SSIS 2012 Copy or Duplicate Environments

  1. Pingback: Jumping from SSIS 2008 to SSIS 2012 | James Serra's Blog

  2. dale says:

    great info

    when i built my package i had configurations for my test and production built in (configuration manager)

    is there a way for those to deploy to the SSIS catalog, or am i stuck adding all the variables in one of the environments there?

    • MikeDavisSQL says:

      You can upgrade the packages to 2012 in either package deployment or project deployment. In project deployment is converts the configurations to parameters. Then you will need to add them to the environments if you want to use them in there.

  3. Pingback: Copy or Duplicate an SSIS environment in the SSIS 2012 Catalog (SSISDB) | svenaelterman

  4. Hi Mike,
    This is a great idea to overcome a limitation of the UI.
    However, your script merely copies the encrypted sensitive value. That is problematic, because sensitive values are encrypted using a key specific to that environment. I.e. each environment has its own encryption key and certificate.
    I have spent some time trying to improve the script and I have posted it here: http://svenaelterman.wordpress.com/2013/05/19/copy-or-duplicate-an-ssis-environment-in-the-ssis-2012-catalog-ssisdb/
    Thanks,
    Sven.

    • MikeDavisSQL says:

      Good stuff Sven, I was not concerned about the sensitive items because that is usually passwords and when moving to a different server the passwords are usually different and have to be reentered. Also, this was for a client and it worked for their needs. Thanks for the additional notes, love the additions.

  5. Henning says:

    Hi Mike,

    I’ve done more or less the same as you but I took the automation part a little further. I haven’t had very many reviews yet so feel free to state your opinion about the script :) -> http://thefirstsql.com/2013/05/28/ssis-2012-easily-copy-environment-variables-to-new-servers-or-new-environments/

  6. Raj More says:

    Thank you. I found this useful.

    How would I extend this to copy configuration (Hardcoded string/int/bool values, EnvironmentVariable assignments, etc) from one Folder-Project combination to another?

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