THE SQL Server Blog Spot on the Web

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

Davide Mauri

A place for my thoughts and experiences on SQL Server, Business Intelligence and .NET

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:

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

image

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

image

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!

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

Comments

 

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:

GRRRRRRRRRRRRRRRRRRRRRREAAAAT!!!!!!

Thanks a lot

March 18, 2011 5:49 AM

Leave a Comment

(required) 
(required) 
Submit

About Davide Mauri

Davide Mauri - MCP, MCAD, MCDBA, MCT, MVP on SQL Server - has worked with SQL Server since version 6.5, and his interests cover the whole platform, from the Relational Engine to Analysis Services, from architecture definition to performance tuning. He also has a strong knowledge of XML, .NET and the Object Oriented Design principles, which allows him to have the correct vision and experience to handle development of complex business intelligence solutions. Having worked as a Microsoft Certified Teacher for many years, Davide is able to pass all his knowledge to his co-workers, allowing his team to deliver high-quality solutions. He currently works as a Mentor for SolidQ and can be found speaking in many Italian and internationals events.

This Blog

Syndication

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement