By: Ray Barley | Updated: 2019-02-19 | Comments (7) | Related: > 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:
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:
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:
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:
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] @folder_name=@FOLDER_NAME, @folder_id=@FOLDER_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]
@environment_name=@TARGET_ENVIRONMENT_NAME,
@folder_name=@FOLDER_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]
@variable_name=@VARIABLE_NAME
, @sensitive=0
, @description=@VARIABLE_DESCRIPTION
, @environment_name=@TARGET_ENVIRONMENT_NAME
, @folder_name=@FOLDER_NAME
, @value=@VARIABLE_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:
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
- Download the T-SQL scripts and experiment with them in your development environment.
- Take a look at the tips SSIS Project Deployment Model - Part 1 and SSIS Project Deployment Model - Part 2 to get the important details on the project deployment model.
- Take a look at the tip Setup Environment Variables in SQL Server Integration Services to review the steps to parameterize your SSIS packages in the development environment and SSMS.
About the author
Ray Barley is a Principal Architect at IT Resource Partners and a MSSQLTips.com BI Expert.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
Article Last Updated: 2019-02-19
