SSIS - Configuring multiple packages with SQL Server

The requirement

The goal of this post is to explain how the SSIS SQL Server configuration can be used to simplify multiple package configuration management.

I eventually faced the requirement of managing a large number of packages, each one with their own configuration driven parameters. This called for a configuration pattern that would allow me to easily configure packages "on-demand" in a single place. This became even more useful when packages had to be moved across my development environment pipeline (Development -> QA -> etc., but that's beyond the scope of this post).

Some background

Let's remember the 5 ways SSIS packages can be configured out of the box:

• XML configuration file (.dtsConfig)
• Environment variable
• Registry entry
• Parent package variable
• SQL Server

The XML configuration file probably sounds like  the most straight forward approach. However, the caveat is that we can't use a single .dtsConfig to manage all packages; every time you configure a package to use an existing .dtsConfig file, the previous configuration is overwritten. Since I didn't want to get into the business of maintaining N .dtsConfig files, I looked for an alternative… this is where the SQL Server configuration came into the picture.

The solution

1. "Make" the packages aware of the ConfigurationDB and their corresponding configured parameters
2. Use the SQL Server configuration to store the package specific information (parameters).
3. Use the Environment Variable configuration to store the location of the ConfigurationDB, this is a one-time setup in the server(s) where the packages are executed. This is only needed if the ConfigurationDB is not hosted by the same server that executes the packages.

The SQL Server configuration allows for multiple packages to be configured in a single table thanks to a Configuration Filter.

Example: configure the packages

Let's configure very simple example. I'll use 2 dummy packages: Package1.dtsx and Package2.dtsx.

I've declared 2 configuration driven variables en each one, they look like this:

NOTES:

1. In our SQL Server configuration we'll create a total of 4 entries, however the value of the variable Config_CommonFileShare is meant to be the same in multiple packages, in other words "shared".

2. As a naming convention, I prepend the variable names with "Config_" to visually know what variables are meant to be initialized by configuration. This becomes very useful once you have a large variable list in a package.

Example: create the SQL Server configuration

Next, create (or reuse) a ConfigurationDB connection manager pointing to the database that will hold your configuration. You don't have to have a dedicated database just for this, since this configuration requires only 1 table, you can use any existing database.

 

We'll now proceed to create the configuration entries for both variables on Package1.dtsx. Follow these steps:

1. Open the Package Configurations

2. Enable them

3. Click Add and Next, you'll get to the following screen. Select the SQL Server configuration type and the ConnectionDB we created previously

4. Click on Add to create a new Configuration Table, you'll see a popup with the T-SQL syntax for the configuration table that will be created, just click OK to accept the default (you may rename the table if you wish). NOTE: you'll only have to create this table once, for subsequent variable configurations, just select it from the drop down

5. Select your configuration filter, this can be any string that uniquely identifies the Package. I recommend using the package name for simplicity and clarity. Afterwards, click Next...

6. Indicate that the properties to configure via this new SQL configuration are the values for our 2 variables. Make sure you only select that. Afterwards, click next. NOTE: We could parameterize the entire variable object, but that would be unnecessary.

7. Give your configuration a name and click finish

The Package Configurations Organizer now has an entry that looks like this

Now for Package2.dtsx...

• Create a ConfigurationDB of its own pointing to the same database (TestDB in this example).
• Repeat the 8 steps above to configure its variables, noticing the following differences:
1. Just reuse the Configuration Table you've created
2. Use a distinctive Configuration Filter (Package2)

Let's examine how have the configurations been declared in the [SSIS Configurations] table. If you followed the steps correctly, the contents should look like this:

Notice how the values have been initialized to whatever we the packages have at the moment. It doesn't matter, we will enter the values we want in this table, the packages will read the configured values every time before being executed.

Let's use the following queries to initialize the values. In a real world situation, these queries should be automatically run by your deployment system (e.g. Team Build).

Now let's create a dummy task in Package1.dtsk, set an OnPreExecute breakpoint and examine the initialized variables. Notice how the variables have been initialized from the values we declared in the SQL Configuration Table. You'll see the same effect if you run Package2.dtsx

Example: using an Environment Variable for database server tracking

Wait a second, what if the ConfigurationDB value changes depending on the environment on which the packages are run (e.g. Development, QA or Production)? How will the packages know where to retrieve the configuration information  from in the first place? The way we've setup our packages so far uses a hardcoded value for ConfigurationDB.

Well, if the packages are hosted in the same box where the ConfigurationDB lives, this is not really a problem; however if the ConfigurationDB is in another server, we want our packages to know which one it is. We achieve this by declaring an Environment Variable in the server where the packages are executed, and link its value to the ConfigurationDB connection manager via an expression. This Environment Variable has to be setup only once per environment.

Perform the following steps:

1. In the server where the SSIS packages will be run, create a System Environment Variable called SSIS_Config (you can name it whatever you want) and indicate the database server name as its value.

2. Create a new Package Configuration for each package indicating to use this new variable. You'll probably have to restart Visual Studio (BIDS) so that it grabs the new variable.

3. Link this new Environment Variable to the ConfigurationDB server name property. Give this configuration a distinctive name such as ConfigDBServerName and click finish.

4. The key now is to make sure this new configuration takes precedence over the other. For this we need to place it at the top of the configuration list. Use the up/down arrows

Working with Source Control

This goes beyond the scope of this document, but if you really have a complex system, you could script out the [SSIS Configurations] table and place it under source control, this way you can keep track of the changes more accurately. Your deployment system should use that script to keep the target configuration consistent at all times.

Conclusion

And that's about it. Your packages can all now be configured in a single place (the [SSIS Configurations] table) and are now ready to be deployed onto any environment.