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.
