SSIS Parent Package Configuration (Gotcha!)

The order of the configurations used in the development studio is not the same as the order used by DTEXEC, specifically in relation to parent package variables.

Parent Package variables always have the lowest priority no matter what order you set in the configuration manager.

Microsoft have recently added a note to BOL on this subject here

Amazon Link: Microsoft SQL Server 2005 Integration Services

The problem with this arises if say you have a parent package and you want to pass to the child package the servername you want it to use for its database configurations. I’ll take an example that I have, which recently ran into this problem.

I have a parent package that calls a series of child packages that take their variable values from the configuration table on a SQL Server. The idea being that the parent package has a ChildConfigurationServer variable and the child package is supposed to set the ServerName property of the Config connection manager according to this value as the first thing it does. Then it can look on the config table of the appropriate server and get the values it needs to run.

What happens instead is the child package is called, it goes and retrieves the values from the config table on the server that was specified and saved at design time. It then either sets all your runtime variables to whatever values are stored in that box or doesn’t set them at all because they don’t exist. Finally it sets the config connection manager with the correct ServerName property (a little too late for it to make any difference!).

There are obvious problems here. If as a lot of teams do, you develop on an environment that gets built from your production environment, the data will look fairly similar. Add to this the fact that a fair few organisations out there run their SSIS packages under a SQL Agent Service account that has priviledges on the development box and voila – you are now configuring your production packages with development variables! Scary stuff!

There is no fix to this as it appears this is the accepted functionality, so if you are wondering if you can get around it – you can’t. What I have had to do to enable my packages to be configured dynamically is to write an XML file in the parent package. I then set the child packages to refer to this same file to set the connection manager ServerName property instead.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.