Copy or Duplicate SQL Server Integration Services (SSIS) Environments using T-SQL

By:   |   Comments (7)   |   Related: > Integration Services Administration


We are using the project deployment model with our SSIS packages. During development we manually setup an environment with variables for each of the project parameters in our packages. How can we create new environments and copy the environment variables using T-SQL rather than doing this manually in SQL Server Management Studio (SSMS)?


The project deployment model provides for deploying SSIS projects to the Integration Services Catalog which is contained in the SSISDB database. From the Integration Services Catalog you can execute SSIS packages, create environments that contain variable values used when executing packages, monitor executing packages, and view many reports on package executions. The Integration Services Catalog became available starting with SQL Server 2012.

An environment contains a list of variables with values to be used for the parameters defined in your SSIS packages that are deployed using the project deployment model. With typical SSIS projects, you will have to setup and configure several environments as a project moves through the development cycle; e.g. development, quality assurance, user acceptance testing and production. You may also have to deploy an SSIS project to a different server.

In this tip I will show you how to write T-SQL scripts to create a new environment by copying an existing environment and its variables as well as update the variables in an existing environment. I find that utilizing T-SQL scripts is much more efficient than performing these tasks manually in SSMS.

Integration Services Project Parameters

Before we get started, there are generally two ways that you define project parameters in an SSIS package during development. To edit the various components in an SSIS package, you right-click on the component and select Edit from the context menu. You will notice that for some components, the context menu will have a Parameterize option. Selecting the Parameterize option will enable you to specify to use a project parameter for a particular property of the component. When you parameterize a component property, the project parameter will be listed in the project parameters for the project. Double-click Project.params in the Solution Explorer (in Visual Studio or SQL Server Data Tools) to see the project parameters:

Project parameters in SSIS project.

The CRM_ServerName in the project parameters above was set using the parameterize option. The CRM_ExportPath was added to the project parameters manually. The first icon at the top left in the project parameters above allows you to add your own project parameters directly. You then use these project parameters in an expression in an SSIS component. For example, I used the CRM_ServerName in the following expression for the ConnectionString property of a flat-file connection manager:

expression builder

While I could have parameterized the ConnectionString property, I only want to set the path of the CSV file via a project parameter and specify the file name in the SSIS package.

Integration Services Catalog

My starting point is an SSIS project with a single package that is deployed to the Integration Services Catalog. If you want to walk through the steps to parameterize an SSIS package, deploy it to the Integration Services Catalog, and create the environment to contain the environment variable values for your project parameters, please take a look at our earlier tip Setup Environment Variables in SQL Server Integration Services.

The following screen shot shows a portion of the Object Explorer pane in SSMS. My sample SSIS project is deployed to the Integration Services Catalog:

Integration Services Catalogs Folders, projects, packages and environments

The main points are:

  • CRM (highlighted) is a folder
  • CRMExport is an SSIS project deployed to the CRM folder
  • ExportCustomers.dtsx is an SSIS package in the CRMExport SSIS project
  • The SSIS project has multiple environments
  • When you execute an SSIS package in the project, you can use one of the environments to supply the values of your project parameters

If you do not see the Integration Services Catalog in SSMS, take a look at this tip for the details on how to create it: SSIS Project Deployment - Part 1.

Integration Services Catalog Queries

In this section we will review the queries that you can use to retrieve the information shown in the Integration Services Catalog screen shot above. These queries will be useful in the T-SQL scripts that copy and update environments. The queries use views in the SSISDB database.

There are many views, stored procedures, etc. in SSISDB. To get started, open SSMS, locate the SSISDB database in the Object Explorer, right-click on the SSISDB database, select New Query, and execute the queries below.

Get the details on the CRM folder:

SELECT [folder_id], [name], [description] 
FROM [catalog].[folders]
WHERE [name] = N'CRM';			

Get the projects in the CRM folder:

SELECT p.[project_id], p.[folder_id], p.[name] 
FROM [SSISDB].[catalog].[projects] p
JOIN [SSISDB].[catalog].[folders] f
   ON p.[folder_id] = f.[folder_id]
WHERE f.[name] = N'CRM';			

Get the packages in the CRMExport project:

SELECT pr.[project_id], pa.[package_id], pa.[name]
FROM [SSISDB].[catalog].[packages] pa
JOIN [SSISDB].[catalog].[projects] pr
   ON pa.[project_id] = pr.[project_id]
WHERE pr.[name] = N'CRMExport';			

Get the environments referenced in the CRM folder:

SELECT e.[environment_id], e.[folder_id], e.[name] 
FROM [SSISDB].[catalog].[folders] f
JOIN [SSISDB].[catalog].[environments] e
   ON e.[folder_id] = f.[folder_id] 
WHERE f.[name] = N'CRM';			

Get the environment variable values for the DEV environment:

SELECT v.[variable_id], v.[name], v.[type], v.[value], v.[description]
FROM [SSISDB].[catalog].[environments] e
JOIN [SSISDB].[catalog].[folders] f
   ON f.[folder_id] = e.[folder_id]
JOIN [SSISDB].[catalog].[environment_variables] v
   ON e.[environment_id] = v.[environment_id]
WHERE f.[name] = N'CRM'
AND e.[name] = N'DEV'			

Copy or Update SSIS Environment with T-SQL Scripts

SSMS provides the capability of adding, updating and deleting the folders, projects, packages, and environments in the Integration Services Catalog of the Object Explorer. You simply right click on an object, select the action from the context menu, and fill in the window that is rendered. While SSMS generally provides a user-friendly interface for performing tasks, T-SQL scripts allow you to easily perform the same task in a repeatable fashion and on multiple servers. In the case of copying an environment, SSMS does not provide an option to perform this task.

Now that we have a quick introduction to the views in the Integration Services Catalog, we can proceed to walk through the T-SQL scripts to copy or update an environment step-by-step. We will reuse some of the queries in the previous section as well as some stored procedures in the Integration Services Catalog. We will walk through the following T-SQL scripts:

  • CopyEnvironmentVariablesToTVC.sql
  • CreateOrUpdateSSISEnvironment.sql


This script gets the environment variables and values from the environment in the folder name that you specify. Typically, this is the environment that you want to copy from. The results from this script will be pasted into the CreateOrUpdateSSISEnvironment.sql script that follows. After you run this script, modify the environment variable values as necessary for either updating the environment or creating a new environment.

Set the values of the @FOLDER_NAME and @SOURCE_ENVIRONMENT in the script below and execute it:


SELECT ',(' +
    '''' + v.[name] + '''' + ',' +
    '''' + CONVERT(NVARCHAR(1024),ISNULL(v.[value], N'<VALUE GOES HERE>')) +
    ''''  + ',' +
    '''' + v.[description] + '''' +
FROM [SSISDB].[catalog].[environments] e
JOIN [SSISDB].[catalog].[folders] f
   ON e.[folder_id] = f.[folder_id]
JOIN [SSISDB].[catalog].[environment_variables] v
   ON e.[environment_id] = v.[environment_id]
AND f.[name] = @FOLDER_NAME
ORDER BY v.[name];			

The script produces the following results:

GetEnvironmentVariablesToTVC query Query results formatted as TVC

The results are formatted so that they can be pasted into the CreateOrUpdateSSISEnvironment.sql script that follows. The results are the environment variable names, values and descriptions separated by commas. Update the values as necessary.


This script is a bit lengthy so we will walk through it a step at a time. The original intent of this script was to create a new environment by copying the environment variables and values from an existing environment. It can also be used to update the environment variable values, and apply the updated values to an existing environment.

Step 1: Set Script Variable Values

Step 1 in the script is used to assign the appropriate values to the @FOLDER_NAME and @TARGET_ENVIRONMENT_NAME variables. The @TARGET_ENVIRONMENT_NAME variable is the environment that you want to create or update. The other variables used in the script are also shown here. UAT is an abbreviation for user acceptance testing. I want to create an environment for that purpose.

 @FOLDER_NAME             NVARCHAR(128) = N'CRM'
,@FOLDER_ID               BIGINT

  [name]        NVARCHAR(128)
, [value]       NVARCHAR(1024)
, [description] NVARCHAR(1024)

Step 2: Load Environment Variables and Values into a Table Variable

Step 2 loads the list of environment variables and values returned by the CopyEnvironmentVariablesToTVC script into a table variable that is used later in the script. Remember that you edit the variable values as appropriate and paste into the script as indicated by the comment below. The CopyEnvironmentVariablesToTVC script puts a comma as the first character of every line; remove the comma from the first line.

SELECT [name], [value], [description]
  -- PASTE the TVC from CopyEnvironmentVariablesToTVC HERE
          ('CRM_ExportPath','C:\OUTBOUND','Path to export CSV files')
        ,('CRM_ServerName','localhost','CRM database server name')
) AS v([name], [value], [description]);

Step 3: Create Folder (if necessary)

Step 3 checks if the folder specified by the @FOLDER_NAME variable exists and creates it if it does not. The @FOLDER_ID variable is set to the value of the existing or new folder and used later in the script.

IF NOT EXISTS (SELECT 1 FROM [SSISDB].[catalog].[folders] WHERE name = @FOLDER_NAME)
    EXEC [SSISDB].[catalog].[create_folder] @folder_name=@FOLDER_NAME, @folder_id=@FOLDER_ID OUTPUT
    SET @FOLDER_ID = (SELECT folder_id FROM [SSISDB].[catalog].[folders] WHERE name = @FOLDER_NAME)			

Step 4: Create Environment (if necessary)

Step 4 checks if the environment specified by the @TARGET_ENVIRONMENT_NAME variable exists in the folder specified by the @FOLDER_NAME variable and creates it if it does not. The @ENVIRONMENT_ID is set to the value of the existing or new environment and used later in the script.

IF NOT EXISTS (SELECT 1 FROM [SSISDB].[catalog].[environments] WHERE folder_id = @FOLDER_ID AND
               name = @TARGET_ENVIRONMENT_NAME)
    EXEC [SSISDB].[catalog].[create_environment]

-- get the environment id
SET @ENVIRONMENT_ID = (SELECT environment_id FROM [SSISDB].[catalog].[environments] 
WHERE folder_id = @FOLDER_ID and name = @TARGET_ENVIRONMENT_NAME)			

Step 5: Create or Update Environment Variables and Values

Step 5 is the final step in the script. At this point both the folder specified by the @FOLDER_NAME variable and the environment specified by the @TARGET_ENVIRONMENT_NAME variable exist. The script iterates through the @ENVIRONMENT_VARIABLES table variable and checks if the environment variable exists in the environment. If the environment variable does not exist, it gets created; otherwise the environment variable value gets updated.

 @VARIABLE_NAME = [name]
,@VARIABLE_VALUE = [value]
,@VARIABLE_DESCRIPTION = [description]
    -- create environment variable if it doesn't exist
      SELECT 1 FROM [SSISDB].[catalog].[environment_variables] 
      WHERE environment_id = @ENVIRONMENT_ID AND name = @VARIABLE_NAME
      EXEC [SSISDB].[catalog].[create_environment_variable]
      , @sensitive=0
      , @description=@VARIABLE_DESCRIPTION
      , @environment_name=@TARGET_ENVIRONMENT_NAME
      , @folder_name=@FOLDER_NAME
      , @value=@VARIABLE_VALUE
      , @data_type=N'String'
    -- update environment variable value if it exists
      EXEC [SSISDB].[catalog].[set_environment_variable_value]
        @folder_name = @FOLDER_NAME
      , @environment_name = @TARGET_ENVIRONMENT_NAME
      , @variable_name = @VARIABLE_NAME
      , @value = @VARIABLE_VALUE
     @VARIABLE_NAME = [name]
    ,@VARIABLE_VALUE = [value]
    ,@VARIABLE_DESCRIPTION = [description]

Execute the Scripts

Open SSMS, open a New Query window for the SSISDB database and execute the scripts. Using the values specified above, you will see the new UAT environment in the CRM folder. Double-click on the UAT environment, select the Variables page, and you will see the environment variables and their values:

New Environment Variable Values After executing the script, view the newly created or updated environment variables.


In this tip I have walked through sample T-SQL scripts that you can use to create, copy and update environments in the Integration Services Catalog. While SSMS provides the ability to do this, I find that in many instances a T-SQL script solution is a better option. In particular T-SQL scripts are extremely easy to use and provide great repeatability.

Next Steps

sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip

About the author
MSSQLTips author Ray Barley Ray Barley is a Principal Architect at IT Resource Partners and a BI Expert.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Comments For This Article

Wednesday, June 5, 2024 - 1:55:49 PM - RussF Back To Top (92294)
This helped me out too. I was running script to copy ssis packages but it didn't address environments.

Tuesday, May 21, 2024 - 2:39:14 AM - SSIS dev Back To Top (92253)
This is such a great help! Really appreciate

Tuesday, April 13, 2021 - 1:40:40 PM - Vazten Back To Top (88523)
Very usefull, thanks

Friday, May 15, 2020 - 7:15:11 PM - RAYMOND BARLEY Back To Top (85677)

It's on my list to handle the other data types. I literally just got bit by this myself this week doing a deployment. 

Friday, May 15, 2020 - 1:39:40 PM - Ulises Back To Top (85674)

hello and thanks,

this works for string types but what is the solution for other data types?

Wednesday, February 20, 2019 - 10:54:26 PM - Lars-Petter Larsson Back To Top (79078)

Great - this will become handy! Thanks for taking the time to create the tip!

Tuesday, February 19, 2019 - 6:26:34 AM - Pavel Pawlowski Back To Top (79062)

 This works nice, but only until there is a sensitive value stored in the environment variable.  Then the content must be first decrypted using the environment symmetric key which is protected by environment certificate. So the universal process is little bit more complicated.

A complete solution then can be similar to tht one: and

get free sql tips
agree to terms