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.
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.
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.
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
MikeDavisSQL 