Much has been said about indirect configurations being
the correct way to configure packages. This is definitely true with SQL 2008 as
they fixed a number of issues with configuring the configuration, i.e. being
able to pass int at runtime the location for the configuration settings.
Unfortunately I'm not using 2008, I'm stuck with SQL 2005 and I hit a rather
annoying feature. You know, one of those features, that is really small but
takes ages to debug. Fortunately I have logging enabled and so it wasn't that
painful. A bit worse than stubbing your toe, but less painful than
getting a hit by football in the crown jewels.
The problem I faced was that a package was failing even though I could see an
error being fired. The first child package was running and reporting a failure.I
looked at my log table and couldn't find anything. some warnings but no
error.
My problem was that I was looking for the error after the package
configuration warnings. We know that missing package configurations don't cause
a package to fail (http://social.msdn.microsoft.com/forums/en-US/sqlintegrationservices/thread/1f7fe0db-a770-40e6-9e27-161af19e8c6f/,
http://consultingblogs.emc.com/jamiethomson/archive/2006/05/25/SSIS_3A00_-Warning-but-no-error-if-an-environment-variable-is-missing.aspx)
When I looked at the configuration warnings I found errors. Go figure.
Well what I fugured was that if your configuration set has settings for a
connection and that connection doesn't exist in your package
then the package will report an error back to the calling parent package. So
this behaviour is different to what happens normally.
Oddly this is only an issue for missing connections (and probably any other
object that doesn't exist), its not an issue for variables because the
Package.Variables object will always exist.
So to fix this just add the connections to your child packages and away you
go, or make your configurations more granular. Its a fine line between having
granular configurations and too many configurations.
...