The Real Order in which SSIS Configurations are applied (2008 / 2008R2)

Mark Wojciechowicz
2014-05-09

I was working on a 2008R2 package, today, which was having some troubles in production.  In looking at the logs I saw first an error:
An OLE DB Error has occurred...Login Failed for user...
And then the warning:
Failed to load at least one of the configuration entries for the package...
In looking at how the package was configured it had two parent package variables which set the database and server names in a variable which were then applied to connection managers in an expression.  There was a 3rd SQL Server configuration which was the one referenced in the warning.  This last variable, by the way, was another connection string subsequently used in an expression for a flat file connection manager.

Now, I could not understand how the package could have succeeded at all, because my understanding of how the configurations are applied was this:
1. The package file is loaded.
2. The configurations specified in the package at design time are applied in the order specified in the package (except for Parent Package Variables).
3. Command line values are applied.
4. The configurations specified in the package at design time are reloaded in the order specified in the package (except for Parent Package Variables). The configurations will use any updated values from the command line when they are reloaded, so it is possible to change the connection string for a SQL Server configuration.
5. Parent Package Variable Configurations are applied.
6. The package is run.
This is taken from here: http://msdn.microsoft.com/en-us/library/cc671625.aspx
Note that parent package variables are only applied in the 5th step, so, in this case, the package should have been using the design time connection and never have been able to apply the correct flat file connection.

However, this package was succeeding and doing the right thing for over a year.  So, I ran a test.  I called the package from a new parent package with more detailing logging and I saw that the parent package variables are indeed applied twice.  I would revise the order to this:
1.     The package file is loaded.
2.     The configurations specified in the package at design time are applied in the order specified in the package.
3.       Parent Package Variable Configurations are applied.
4.       Command line values are applied.
5.       The configurations specified in the package at design time are reloaded in the order specified in the package.  The configurations will use any updated values from the command line when they are reloaded, so it is possible to change the connection string for a SQL Server configuration.
6.       Parent Package Variable Configurations are applied again.
7.       The package is run.
 Oddly, although there was a login error at step 2, the package did not fail.  Instead, the parent package variables were applied in step 3 to name the correct server and the connection succeeded at step 5.

Labels: