Friday 17 December 2010

SSIS: Orphaned Configuration Connections

Although this issue seems to have been around a while, there doesn't seem to be stack loads of information on the web.

If you use configurations in SSIS package, in SQL 2008 an error is thrown if a connection property exists in the configuration, but not in the package. In other words, your package is expecting to find every connection that is referenced in the configuration and if it can't find it, it will throw an error.

The connection "YOURCONNECTIONNAME" is not found. This error is thrown by Connections collection when the specific connection element is not found. (Microsoft.DataTransformationServices.VsIntegration)

So, a typical scenario may be that you have a single configuration file which holds all your connection strings. It stands to reason that not all your packages will use all connections but SQL2008 packages will still expect them to be defined in your package.

This is a breaking change from SQL2005 where such a scenario would throw a warning not an error. To me, the SQL2005 behaviour makes more sense and i'll be interested to see if they revert this.

The workaround to the problem is to increase your configurations, and perhaps have a single configuration for each connection. This is probably workable in many scenarios but I can see this being quite tiresome if you have many connections and many packages.

No comments:

Post a Comment

/* add this crazy stuff in so i can use syntax highlighter