Child vs Parent Package Configurations Part 1

One of my colleagues - Sam Loud - prompted a mention from the venerable Jamie Thompson on the thorny subject of SSIS configuration (in fact he starts with the problems with logging configuration - something I've also encountered, but the subject for another post I think). In fact we were debating it in the office that very day .

Sam's beef with parent package configurations is that it makes the development of child packages more onerous. This is due to the inability of child packages to use master configurations at run-time, unlike a direct configuration to a config file or database table which the child package can use. An example of this follows:

Firstly create a new SSIS project in BIDS/Visual Studio and create two packages - 'Parent' and 'Child':



We'll create a very simple child package to read the @@VERSION variable in SQL:

Add a connection manager to the package pointing to the master database on a convenient server (in this case my local machine):



Add an Execute SQL Task selecting the connection manager created above and type "SELECT @@VERSION" as the SQLStatement property:



Create a package variable with a 'Child' scope called 'ConnString', and set the connection manager to use the variable through the Property Expressions Editor:



You'll note at this point that you need to type the connection string into the variable to get the package to work. This of course is what Sam is complaining about having to do with Master configurations. However the advantage becomes clearer when adding a second child package.

Finally create a simple configuration using an XML file to set the ConnString variable value:



Executing the package should result in success. This is an example of a direct child configuration. Adding a second child package - Child2 - running through the same steps as for Child you can skip the step typing the connection string into the variable - simply add the variable and the configuration (remembering to click 'Reuse' when prompted). You should be able to execute Child2 successfully with the package picking the connection string up from the config file.

Now for the Parent configuration:

In the Parent package create a variable called 'ConnString' with Parent scope and use the XML configuration created for the Child.dtsx package. Create a File Connection Manager, selecting 'Existing file' in the drop-down and the file location of Child.dtsx (I'm not using MSDB for this example though this might be advisable in practice). Add a Execute Package task using the File Connection Manager created earlier.

Switch to the Child.dtsx package and remove the file configuration created earlier and create a 'Parent package variable' configuration adding 'ConnString' as the Parent variable:



You should be able to execute the Parent package and see BIDS switch to the Child.dtsx package and execute that in turn.

But what happens when you replace the file configuration in Child2.dtsx, with Parent package variable and try and execute it by itself? It will fail - as the ConnString variable in Child2.dtsx is blank and it will not 'look-up' the variable in Parent.dtsx. To get it to work you need to manually type the connection string into the ConnString variable. This is the crux of Sam's complaint. Using parent configuration means typing the connection string every time you create a package rather than just the once. There are associated disadvantages in that you can't test the development configuration until the parent package is run and so on.

The advantages of parent package configuration relate to larger, more complex projects:

1. Configuration is centralised and 'hidden' from child packages:

This makes changing the configuration (from database to environment variable for example)infinitely easier if you have a large number of packages. This might be an issue you can't control the environment your packages are running on, or you have multiple different possible environments to target.

2. Development amongst multiple developers without direct configuration is easier:

In my experience when developing packages in a team of 3 or more people direct configurations can become a pain. This is unless everyone has their machine set up exactly the same way. I've often found myself disabling the direct configurations in order to get a package to use a different database (local rather than server-based for example) or filepath to the one specified in the configuration. Using parent configurations leaves you as a developer free to set the variables as you please.

So in conclusion - because this post has gone on long enough - use child packages with direct configuration when the project is small, the environments you're using are well-defined and the number of packages are low - probably no more than 6-8. Otherwise the safest course of action is parent configuration.

In a future post I'll look at the possible solutions and/or work-arounds for working with parent configurations without typing configuration in for every package.