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

 

Setup Environment Variables in SQL Server Integration Services 2016


By:   |   Read Comments (2)   |   Related Tips: More > Integration Services Configuration Options

Attend our free MSSQLTips Webcast - How to Simplify Routine SQL Server Administration Tasks


Problem

Iíve created a SQL Server Integration Services (SSIS) project in Visual Studio. I added a couple of packages and now I want to deploy everything to the server. How can I change my connection strings dynamically? I donít want to update all my packages manually after theyíre deployed to the server.

Solution

Starting with SQL Server Integration Services 2012, we can use the project deployment model. In this model, we have environments and environment variables at our disposal. Together with parameters, they give us a convenient, flexible and powerful way to dynamically change the behavior of SSIS packages.

Do not mistake the SSIS environment variables of the project deployment model with environment variables of the Windows operating system. In SQL Server 2005 and 2008 we could use those variables to configure our packages as well, in combination with configuration files or tables. This is still possible when you convert your project from the project deployment model to the legacy package deployment model. For the remainder of this tip, we will work with the project deployment model, as it is easier to work with, more flexible and more robust.

To illustrate the use of environment variables, weíll follow this process:

  1. Configure a project connection manager with a project parameter
  2. Deploy the project to the catalog
  3. Create an environment in the SSIS catalog, along with a variable
  4. Link the environment to the project and the variable with the parameter
  5. Execute a package using the environment

SSIS Project and Package Set-up with Parameters

The easiest way to work with environment variables is first to use parameters in your packages. Parameters are like SSIS variables, except they cannot change value during the execution of the package. Parameters are excellent constructs to pass values into a package at the start of an execution and to control its behavior. You can have parameters at the package level and at the project level.

Suppose I have an SSIS project with some packages.

ssis project tree

When you double click on Project.params, a tab will open where you can create project parameters. Letís create a parameter that will hold the name of the server that holds the Wide World Importers database.

ssis project parameter

Setting the property Required to True means we need to change the value of the parameter after deployment. Failing to do so will result in an error when the package is executed. This avoids the scenario where packages are being run with design-time values and you are changing data on the wrong server. Now we can use this parameter to create an expression in the project connection manager. When the parameter changes its value, the connection manager will change accordingly. You can assign an expression to a project manager easily by right-clicking it in a package and selecting ParameterizeÖ

parameterize

In the dialog, we set the ServerName property to our project parameter.

configure servername with project param

Now we can easily switch between servers by changing the parameter value. You can easily notice when a connection manager is configured by an expression: a little fx icon appears.

fx icon

Development is done, now we can deploy the project to the SSIS catalog. We can take a look at our project parameter by right-clicking the project and selecting ConfigureÖ

configure ssis project

In the dialog, we can see our project parameter. The value is empty because itís a required parameter and we need to explicitly specify a value after deployment.

catalog project parameter

By clicking on the ellipsis, we can configure the value. The default value is greyed out since the parameter is required.

However, what if you have 5 different servers on which your package can run? Do you want to manually change the parameter value each time? Or create several T-SQL scripts to change it every time a package is being executed? To solve this issue, environments were created.

Create Environment in SSIS

You can look at an environment as a collection of configuration properties, much like a configuration file or table in the legacy package deployment model. In every folder, you can create one or more environments.

create environment

In the dialog, you can give the environment a name and a description.

create environment

If you want to create this environment on multiple servers (aka multiple SSIS catalogs), donít hit OK just yet. You can script out the necessary T-SQL using the Script option.

script ssis environment

The result looks like this:

tsql create environment

The problem is that the Script option only scripts out changes. So, the entire environment will be scripted at creation time. However, if you try this at a later point in time (when there are no changes), an empty query window will be opened. If you want to script out existing environments, youíll need to look at 3rd party tools or write your own using T-SQL, .NET or PowerShell.

The next step is to add an environment variable to the environment. When you go back to the environment properties, a tab will be added for the variables. Here we can add a variable to configure our project parameter.

create variable

Remember, if you want to script it out, you need to do so when you have just created the variables and you are still in the dialog!

tsql create environment variable

Configuring the Parameter with the Environment Variable

The last step is to link the environment variable to our project parameter. The goal is that when we execute a package, we can switch between environments and easily influence the behavior of a package.

First, we need to create a reference between the project and the environment. You can do this by going back to the project configuration. In the References tab, we can add a reference. This can either be a local reference or an absolute reference. Absolute references remain valid and keep pointing to the same environment, even if we move a project to another folder. Local references will search for an environment of the same name in the current folder.

create environment reference

The last step is to link the environment variable to the parameter. This is done in the Set Parameter Value dialog of the parameter.

set parameter to env variable

If you want your parameter to be configurable by multiple environments, all environments need to have an environment variable that shares the same name. Donít forget to script everything out if needed!

link environments and params

When we execute a package, we must now select an environment. By doing so, the value of the environment variable will be passed to the parameter. When the package runs, the value of the parameter will be used to set the project connection manager. If we would select a different environment, we can force the SSIS package to run on another server.

select environment

After the package has run, we can verify the value of the environment variable was used in the Overview report:

overview report

Conclusion

Using environments and environments variables we can dynamically configure the behavior of SSIS packages. The advantage of using environments is to have a central storage of all configurations which can easily be reused across different projects. By using multiple environments, you can implement different execution scenarios for your SSIS packages.

Next Steps


Last Update:


signup button

next tip button



About the author
MSSQLTips author Koen Verbeeck Koen Verbeeck is a BI professional, specializing in the Microsoft BI stack with a particular love for SSIS.

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    Notify for updates 


SQL tips:

*Enter Code refresh code     



Friday, June 09, 2017 - 9:44:52 AM - Koen Verbeeck Back To Top

Hi Stephen,

 

there is no real benefit that I can think of. I needed an example to illustrate the interaction between parameters and environments and this was the first thing I came up with :)

That being said, I do like all my parameters/configurations to be centralized in one place.

Regards,
Koen


Thursday, June 08, 2017 - 4:15:11 AM - Stephen Back To Top

Nice article.

I was wondering if there is a reason why you did not map your server name variable to the connection manager directly in the configure step; rather than configuring paramaters at design time to do this? This is the approch that I have taken and was wondering if there is a benefit to doing it the way that you have sugeseted?

 

 


Learn more about SQL Server tools