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? After deploying all the packages to the server, I prefer not to update them manually. Learn how by using SSIS environment variables.
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:
- Configure a project connection manager with a project parameter
- Deploy the project to the catalog
- Create an environment in the SSIS catalog, along with a variable
- Link the environment to the project and the variable with the parameter
- Execute a package using the environment
SSIS Project and Package Set-up with Parameters
The easiest way to work with environment variables is to first 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 the project level.
Suppose I have an SSIS project with some packages.
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.
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 run with design-time values and data changes occur 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…
In the dialog, we set the ServerName property to our project parameter.
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.
We have completed the development. 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…
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.
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 executes? To solve this issue, create environments.
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.
In the dialog, you can give the environment a name and a description.
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.
The result looks like this:
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 opens. 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 is added for the variables. Here we can add a variable to configure our project parameter.
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!
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.
The last step is to link the environment variable to the parameter, located in the Set Parameter Value dialog of the parameter.
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!
When we execute a package, we must now select an environment. By doing so, the value of the environment variable passes to the parameter. When the package runs, the value of the parameter helps to set the project connection manager. If we select a different environment, we can force the SSIS package to run on another server.
After the package has run, we can verify the value of the environment variable was used in the Overview report:
Conclusion
Using environments and environment 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
- If you want to learn more about the project deployment model, you can read more about it in the tips SSIS Project Deployment Model in SQL Server 2012 (Part 1 of 2) and part 2.
- If you are interested in the legacy package deployment model and its package configurations, read more in SQL Server Integration Services SSIS Package Configuration.
