Configuring SSIS with Environment Variables

Yep it’s more SSIS again this week. Here we’ll be looking at using Environment configuration within the SSIS catalog. This allows sets of parameters to be defined and used across multiple projects and packages which share common values.

This approach can either be used as a central point for configuration, or you could use multiple configurations for the same packages. For example:

• a process which run the same workflow but with different parameters on a daily, weekly, or monthly basis
• or running a centralised SSIS deployment covering both development and test which require different parameters

Last week we looked at how to manage SSIS parameters using Visual Studio’s Configurations which would maintain these values in the development environment. Here the configuration being on the server side.

We’re using the same project again so we’ll have a couple of project parameters to configure:

We also have a shared connection in this project which will be considered too. Unlike the Visual Studio Configurations we looked at, this approach can support configuration of the connections:

Let’s go.

Environments

Environments are created within the Integration Services Catalog, inside a folder alongside projects. We’ll get straight in to creating a new environment:

I’ll create Development and Test ones for the demonstration. Once created they will appear under the Environments node. Re-opening them we’ll see a different dialog which has a Variables page available. This is where we can start configuring.

The variable creation is similar to Variables and Parameters we’re used to from SSIS. We have flexibility with the naming as an environment isn’t specifically tied to any Project or Package. I’ll create a couple similar to our project:

The test environment is set up in the same way. Variable names should be consistent across environments which will be used for the same project. Referencing a variable is done by name. When we want to execute a package with a different environment we want to make sure the same variable is present with a different value. Here’s the Test setup:

With those set up, let’s go and configure our project to reference the environments and variables.

Configuration

To use the environments and their variables we need to reference them from our Project. To do that we head into the usual Configure dialog but this time start with the References page. In here we can choose which Environments can be referenced. In our case, both:

With the references in place we can look at using those variables for our parameters.

Back to the Parameters page and selecting a value as we would to configure it we now have a new option to ‘Use environment variable’ available to us:

We can do this with our parameters to tie them to the variables. You’ll see that nowhere is the configuration concerned with what the values for the variables are. They just need a pointer to the variable to use. Once configured our project would look like this:

With these variables set in place we don’t need to worry about how parameters are configured for the project when building and running locally. Once they’re published to the server these variables will take over the values. You’ll just need to pop back into the Environments and Configure dialogs when you add new parameters.

That’s all there is to it with our configuration side. Now we need to look at how we choose which Environment to use when running the packages.

Execution

Unlike static parameters which we can see within the Configure dialog, we’re now referencing variables which can exist in multiple environments. We need to select which Environment to use when the package runs.

We’ll create a SQL Agent job step to execute the package.

When creating the step, the parameter values will be empty and we need to tick the Environment option and choose which set of variables to use:

That’s all there is to it, you’ll see the variable names appear in the parameter values and we’re good to go.

About connections

Earlier we mentioned that the use of Environments can allow us to configure Connections too. But we haven’t seen those anywhere. So…

Environments themselves only contain variables, they don’t have a concept of connections. What we can do however is configure the Connection String property for our connection to use a variable.

We’ll set up a connection string for our Environment:

Within the Project configuration we can head into the Connection Manager tab and configure our Connection String based on a variable like we would with other parameters:

Et voila!

As with other variables, ensure they’re set up across each environment and are configured accordingly.

Wrap up

In this post we’ve looked at using Environments to store multiple sets of configuration within SSIS. We demonstrated how this could be used in a SSIS instance which is utilised for executing both development and test jobs.

Without this configuration you may have multiple SQL Agent jobs where parameters would be configured individually. This configuration wouldn’t reside with the source code not the SSIS catalog.

With this approach only a single parameter (the Environment) would differ between the jobs. All configured values are maintained within the SSIS catalog as the packages, configuration, and environments go hand in hand.

Another benefit is that it doesn’t matter how the SSIS project is configured in a local development environment, as when it gets deployed it’ll retain the environment variable mappings already in place.

So I guess there’s only one question left; how do you eat configure yours?