THE SQL Server Blog Spot on the Web

Welcome to - The SQL Server blog spot on the web Sign in | |
in Search

Davide Mauri

A place for my thoughts and experiences the Microsoft Data Platform

Auto-Configuring SSIS Packages

SSIS Package Configurations are very useful to make packages flexible so that you can change objects properties at run-time and thus make the package configurable without having to open and edit it.

In a complex scenario where you have dozen of packages (even in in the smallest BI project I worked on I had 50 packages), each package may have its own configuration needs. This means that each time you have to run the package you have to pass the correct Package Configuration. I usually use XML configuration files and I also force everyone that works with me to make sure that an object that is used in several packages has the same name in all package where it is used, in order to simplify configurations usage. Connection Managers are a good example of one of those objects. For example, all the packages that needs to access to the Data Warehouse database must have a Connection Manager named DWH.

Basically we define a set of “global” objects so that we can have a configuration file for them, so that it can be used by all packages.

If a package as some specific configuration needs, we create a specific – or “local” – XML configuration file or we set the value that needs to be configured at runtime using DTLoggedExec’s Package Parameters:

Now, how we can improve this even more? I’d like to have a package that, when it’s run, automatically goes “somewhere” and search for global or local configuration, loads it and applies it to itself.

That’s the basic idea of Auto-Configuring Packages.

The “somewhere” is a SQL Server table, defined in this way


In this table you’ll put the values that you want to be used at runtime by your package:


The ConfigurationFilter column specify to which package that configuration line has to be applied. A package will use that line only if the value specified in the ConfigurationFilter column is equal to its name. In the above sample. only the package named “simple-package” will use the line number two.

There is an exception here: the $$Global value indicate a configuration row that has to be applied to any package. With this simple behavior it’s possible to replicate the “global” and the “local” configuration approach I’ve described before.

The ConfigurationValue contains the value you want to be applied at runtime and the PackagePath contains the object to which that value will be applied. The ConfiguredValueType column defined the data type of the value and the Checksum column is contains a calculated value that is simply the hash value of ConfigurationFilter plus PackagePath so that it can be used as a Primary Key to guarantee uniqueness of configuration rows.

As you may have noticed the table is very similar to the table originally used by SSIS in order to put DTS Configuration into SQL Server tables:

SQL Server SSIS Configuration Type:

Now, how it works?

It’s very easy: you just have to call DTLoggedExec with the /AC option:

DTLoggedExec.exe /FILE:”mypackage.dtsx” /AC:"localhost;ssis_auto_configuration;ssiscfg.configuration"

the AC option expects a string with the following format:


only Windows Authentication is supported.

When DTLoggedExec finds an Auto-Configuration request, it injects a new connection manager in the loaded package. The injected connection manager is named $$DTLoggedExec_AutoConfigure and is used by the two SQL Server DTS Configuration ($$DTLoggedExec_Global and $$DTLoggedExec_Local) also injected by DTLoggedExec, used to load “local” and “global” configuration.

Now, you may start to wonder why this approach cannot be used without having all this stuff going around, but just passing to a package always two XML DTS Configuration files, (to have to “local” and the “global” configurations) doing something like this:

DTLoggedExec.exe /FILE:”mypackage.dtsx” /CONF:”global.dtsConfig” /CONF:”mypackage.dtsConfig”

The problem is that this approach doesn’t work if you have, in one of the two configuration file, a value that has to be applied to an object that doesn’t exists in the loaded package. This situation will raise an error that will halt package execution.

To solve this problem, you may want to create a configuration file for each package. Unfortunately this will make deployment and management harder, since you’ll have to deal with a great number of configuration files.

The Auto-Configuration approach solve all these problems at once!

We’re using it in a project where we have hundreds of packages and I can tell you that deployment of packages and their configuration for the pre-production and production environment has never been so easy!

To use the Auto-Configuration option you have to download the latest DTLoggedExec release:

Feedback, as usual, are very welcome!

Published Wednesday, March 16, 2011 4:32 PM by Davide Mauri

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS



csm said:

Great job, Davide!! It really saves a lot of work, and I'll use in the project I'm currently working on.

Just one thing: I think that there's an error in the definition of the table, because you define an unique constraint on the column ConfigurationFilter, so you can only have one parameter's value for each package. Maybe ConfigurationFilter + PackagePath is a better approach, could be?

Also, it's possible that I'm wrong...

March 16, 2011 10:19 AM

Davide Mauri said:

Hi csm

you're completely right! The screenshot was taken from a test database that I forgot to update. I've updated the post so that now it shows the correct screenshot.

Thanks a lot!

March 16, 2011 1:27 PM

csm said:

Doesn't matter.

Just another question: is possible to have the same variable name, both in Global and in an individual package? What's happen in that case?

Could be a great feature if the "local" overwrite the "global" one

March 17, 2011 4:42 AM

Davide Mauri said:

Hi csm, yes, it work exactly in that way: "local" configuration take precedence over "global" configurations.


March 18, 2011 5:19 AM

csm said:


Thanks a lot

March 18, 2011 5:49 AM

abx said:


May 31, 2018 10:58 PM

linying123 said:



July 16, 2018 9:30 PM

dongdong8 said:



July 23, 2018 11:47 PM

chenjinyan said:


August 22, 2018 11:16 PM

shenyuhang said:


August 23, 2018 10:31 PM

chenjinyan said:



October 9, 2018 6:48 PM

chenqiuying said:


October 10, 2018 6:28 PM

chenlixiang said:


November 20, 2018 10:52 PM

Leave a Comment


About Davide Mauri

Director of Software Development & Cloud Infrastructure @ Sensoria, an innovative smart garments and wearable company. After more than 15 year playing with the Microsoft Data Platform, with a specific focus on High Performance databases, Business Intelligence, Data Science and Data Architectures, he's now applying all his skills to IoT, defining architectures to crunch numbers, create nice user experiences and provide meaningful insights, all leveraging Microsoft Azure cloud. MVP on Data Platform since 2006 he has a very strong background development and love both the ER model and OO principles. He is also a fan of Agile Methodology and Automation, which he tries to apply everywhere he can, to make sure that "people think, machines do".

This Blog


Privacy Statement