Copy environment variables between SSIS catalogues on different servers

Problem

Usually when developing SSIS packages, you want the project to run on different servers, e.g. development, test and production server. If you have many environment variables creating all of them is tedious and unnecessary work. I’ll show you how you can copy environment variables between SSIS catalogues that are located on different servers.

Solution

To solve this, you can link your servers and copy the variables directly using an SQL query. In this example I will copy variables from one server (let’s call it SOURCE_SERVER) to my other server (TARGET_SERVER). I executed all this queries on the TARGET_SERVER, but I guess it also works the other way around if you modify the script a little.

Step 1: Communicating with another server

Linking servers

If you haven’t done this before, you need to link your server (TARGET_SERVER) to the second server (SOURCE_SERVER).
You can check first whether the server is already linked:


select * from sys.servers;

I executed this on TARGET_SERVER:


EXEC sp_addlinkedserver @server = 'SOURCE_SERVER', @srvproduct='SQL Server';

  1. @server: the name of the second server
  2. @srvproduct: if you set this to ‘SQL Server’ you don’t need to set anything else

Now check again by querying the sys.servers whether the table is linked! NOTE: This relationship is unidirectional, executing sp_addlinkserver will only create an entry in sys.servers of the TARGET_SERVER.

List of linked SQL Servers.
List of linked SQL Servers.

Executing queries on another server

Now you can execute queries on the other server.


select * from openquery(SOURCE_SERVER, 'select * from SSISDB.internal.environment_variables')

NOTE: You don’t need quotation marks around the name of the server!

Step 2: Copying environments

As a basis for my script I used one from the blog post SSIS 2012 Copy or Duplicate Environments | Mike Davis SQL but I extended it to work across servers and to only load new variables.

Creating an empty environment

First we need to create an empty environment on the TARGET_SERVER. Open the properties and remember the identifier. Also lookup the identifier of the environment that you want to copy.

Properties of the new SSIS environment.
Properties of the new SSIS environment.

Copy all the parameters

With the following script you can copy all environment variables from one environment (@env_id_old – SOURCE_SERVER) to the newly created environment (@env_id_new – TARGET_SERVER). Just change the two variables to the identifiers you just looked up.


DECLARE @env_id_old INT = 1;
DECLARE @env_id_new INT = 1;
INSERT INTO [internal].[environment_variables]
([environment_id]
,[name]
,[description]
,[type]
,[sensitive]
,[value]
,[sensitive_value]
,[base_data_type])
SELECT @env_id_new as environment_id --New Environment ID
,[name]
,[description]
,[type]
,[sensitive]
,[value]
,[sensitive_value]
,[base_data_type]
FROM openquery(SOURCE_SERVER, 'select * from SSISDB.internal.environment_variables')
where environment_id = @env_id_old --Previous Environment ID

Only copy new variables

Later I added some new variables to my old environment which I wanted to copy to the new environment. To copy only the new ones, I updated the script a little.


DECLARE @env_id_old INT = 1;
DECLARE @env_id_new INT = 1;
INSERT INTO [internal].[environment_variables]
([environment_id]
,[name]
,[description]
,[type]
,[sensitive]
,[value]
,[sensitive_value]
,[base_data_type])
SELECT @env_id_new as environment_id --New Environment ID
,[name]
,[description]
,[type]
,[sensitive]
,[value]
,[sensitive_value]
,[base_data_type]
FROM SOURCE_SERVER.SSISDB.internal.environment_variables ev_new
WHERE environment_id = @env_id_old AND --Previous Environment ID
NOT EXISTS (SELECT 1 FROM [internal].[environment_variables] ev_old WHERE
ev_new.[name] = ev_old.[name]);

Interesting parts:

  1. FROM SOURCE_SERVER.SSISDB.internal.environment_variables ev_new -> using openquery didn’t work here, but I found out that you can use the name of other servers directly as a part of the identifier (as soon as they are linked)
  2. There exist different practices on how to check whether a row to insert already exists. I already have used this syntax before and since this query is not time critical I stuck with it. Feel free to improve this query by using other ways (e.g. MERGE)
  3. NOTE: I only compare the names of the environment variables but your situation might be different. Before you execute this query you should just execute the second part (SELECT …) and check whether it returns the expected output.

Step 3: Configure the package

Don’t forget to add the environment to the package! It was a long time ago since I configured the SOURCE_SERVER, so I was surprised that my variables didn’t show up in the configuration window. To add the environment and make the variables available > right click the project > Configure … > References and then add the newly created environment.

SSIS package configuration
Last step: configure package with new variables

Don’t forget to update parameters related to the server name! You probably have some database connection parameters and those likely contain the name of the SOURCE_SERVER. Don’t forget to update them to the new server.

Happy loading!

Further info

Leave a Reply

Your email address will not be published. Required fields are marked *