Execute Package Task Packages fail for no apparent reason

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 figured 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.

 


Published Tuesday, March 2, 2010 10:35 AM by simonsabin

Comments

Wednesday, March 3, 2010 5:21 AM by SqlServerKudos

# Execute Package Task Packages fail for no apparent reason

Kudos for a great Sql Server article - Trackback from SqlServerKudos

# Do You Want Mass Traffic To Your Website? | Online Marketing Journey

Pingback from  Do You Want Mass Traffic To Your Website? | Online Marketing Journey

# Do You Want Mass Traffic To Your Website? | Online Marketing Journey

Pingback from  Do You Want Mass Traffic To Your Website? | Online Marketing Journey