SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

SSIS R2 Configuration File Issue Fixed

Using configurations files in SSIS is a great way to change how your package will run from outside the development environment.  One thing that I’ve always recommended when using files (I actually prefer to use config tables) is to have one file for each of the connection managers in the package. 

The reason I have always recommended this instead of one file for all your packages is because previously in SSIS 2005 and 2008 if you used one file for all your packages then you must include all the connection manager from the file in all your packages for it to actually run.  So if you decided it was easier to maintain one file for all your connections SSIS would force you to use every connection in all packages regardless of whether or not the package actually used it.  If you did not include all the connection that are in the file then SSIS will throw an error telling you that you are missing the connection.

In SQL Server 2008 R2 this problem is actually fixed.   I didn’t realize there were any design changes with SSIS in R2 but this is actually a pretty helpful one that I ran into while trying to demonstrate the problem with config files previously.  Now when you use config file you can store all your connections in a single file and SSIS will actually run the packages successfully if you do not have all those connections in the package.

You actually do still see an error but not until you open the configuration manager.  Again my preference is to use the database storage method for configurations but if you’re using this option I hope this helps.

Devin Knight

Devin is a BI consultant at Pragmatic Works Consulting. Previously, he has tech edited the book Professional Microsoft SQL Server 2008 Integration Services and was an author in the book Knight's 24-Hour Trainer: Microsoft SQL Server 2008 Integration Services. Devin has spoken at past conferences like PASS and at several SQL Saturday events. He is a contributing member to the Business Intelligence Special Interest Group (SIG) for PASS as a leader in the SSIS Focus Group. Making his home in Jacksonville, FL, Devin is a participating member of the local users’ group (JSSUG).


Posted by ken.trock on 5 October 2010

Wow, a seperate config file for each connection manager. I find config files a little unwieldy to begin with but they beat the alternative of hardcoding data sources.

Leave a Comment

Please register or log in to leave a comment.