Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

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


By:   |   Last Updated: 2019-02-19   |   Comments (2)   |   Related Tips: More > Integration Services Administration

Problem

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)?

Solution

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

CopyEnvironmentTariablesToTVC.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:

DECLARE @FOLDER_NAME NVARCHAR(128) = N'CRM';
DECLARE @SOURCE_ENVIRONMENT NVARCHAR(128) = N'DEV';

SELECT ',(' +
    '''' + v.[name] + '''' + ',' +
    '''' + CONVERT(NVARCHAR(1024),ISNULL(v.[value], N'<VALUE GOES HERE>')) +
    ''''  + ',' +
    '''' + v.[description] + '''' +
    ')' ENVIRONMENT_VARIABLES
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]
WHERE e.[name] = @SOURCE_ENVIRONMENT
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.

CreateOrUpdateSSISEnvironment.sql

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.

DECLARE
 @FOLDER_NAME             NVARCHAR(128) = N'CRM'
,@FOLDER_ID               BIGINT
,@TARGET_ENVIRONMENT_NAME NVARCHAR(128) = N'UAT'
,@ENVIRONMENT_ID          INT
,@VARIABLE_NAME           NVARCHAR(128)
,@VARIABLE_VALUE          NVARCHAR(1024)
,@VARIABLE_DESCRIPTION    NVARCHAR(1024)

DECLARE @ENVIRONMENT_VARIABLES TABLE (
  [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.

INSERT @ENVIRONMENT_VARIABLES
SELECT [name], [value], [description]
FROM (
  VALUES
  --
  -- 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]);
 
SELECT * FROM @ENVIRONMENT_VARIABLES;  -- debug output			

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] @[email protected]_NAME, @[email protected]_ID OUTPUT
ELSE
    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]
     @[email protected]_ENVIRONMENT_NAME,
     @[email protected]_NAME

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

SELECT TOP 1
 @VARIABLE_NAME = [name]
,@VARIABLE_VALUE = [value]
,@VARIABLE_DESCRIPTION = [description]
FROM @ENVIRONMENT_VARIABLES
WHILE @VARIABLE_NAME IS NOT NULL
BEGIN
   PRINT @VARIABLE_NAME
    -- create environment variable if it doesn't exist
   IF NOT EXISTS (
      SELECT 1 FROM [SSISDB].[catalog].[environment_variables] 
      WHERE environment_id = @ENVIRONMENT_ID AND name = @VARIABLE_NAME
   )
      EXEC [SSISDB].[catalog].[create_environment_variable]
        @[email protected]_NAME
      , @sensitive=0
      , @[email protected]_DESCRIPTION
      , @[email protected]_ENVIRONMENT_NAME
      , @[email protected]_NAME
      , @[email protected]_VALUE
      , @data_type=N'String'
   ELSE
    -- 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
   DELETE TOP (1) FROM @ENVIRONMENT_VARIABLES
   SET @VARIABLE_NAME = null
   SELECT TOP 1
     @VARIABLE_NAME = [name]
    ,@VARIABLE_VALUE = [value]
    ,@VARIABLE_DESCRIPTION = [description]
    FROM @ENVIRONMENT_VARIABLES
END			

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.

Conclusion

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


Last Updated: 2019-02-19


next webcast button


next tip button



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

View all my tips




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



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

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

 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: https://pawlowski.cz/2016/10/10/clone-copy-ssis-server-variables-among-environments-sp_ssiscloneenvironment-on-github/ and https://github.com/PavelPawlowski/SQL-Scripts/blob/master/SSISDB/sp_SSISListEnvironment.sql


Learn more about SQL Server tools