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: http://dtloggedexec.davidemauri.it/Package%20Parameters.ashx 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: http://msdn.microsoft.com/en-us/library/ms141682.aspx 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: <database_server>;<database_name>;<table_name>; 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: http://dtloggedexec.codeplex.com/releases/view/62218 Feedback, as usual, are very welcome!
