I just discovered something rather annoying...and hopefully someone has found a way around this! I have an SSIS solution I've created that has about 75 different SSIS packages. I need to create a test solution in order to do some maintenance. I've created a test database for use with the project. In the project, I've created some data sources for use in Connection Managers. I've gone ahead and changed the data sources to point to the test databases. The issue is that the connection managers do not get updated with the new data source information. I really don't feel like going through and upating every single connection manager in each package. Is there an easier way to update the info contained in the connection manager?? Thanks!!A. M. Robinson

you can use Package Configurations, in package configuration (it can be xml configuration or sql server configuration) you can set value to connection manager's properties like connection string, initialCatalog, username, password .... now if you want to use a configuration for all packages, just use standard naming, for example if you have a sourcedatabase configuration which is same in all packages, name it like SourceCnnDB then in package configuration when you change that values, and all packages use that configuration then all packages will be updated when they are going to execute. more about package configuration: http://sqlblog.com/blogs/eric_johnson/archive/2009/09/21/using-ssis-package-configurations.aspxhttp://www.rad.pasfu.com

With this approach, I still have to go into each individual package and define the configuration file. Is there some reason why SSIS is not smart enough to know that I changed the data source and thusly reflect those changes in the package connection manager? And if this is "standard" behavior, then why even bother having data sources to begin with?A. M. Robinson

NO need to define the config file anew for each package, but just once, thing is, you can re-use it, but you DO need to define it for each package. See http://support.microsoft.com/kb/955709 Btw, SSIS 2012 has an advantage for you in letting to define a project level variable/parameter (e.g. a conn string that is shared among all packages).Arthur My Blog

NO need to define the config file anew for each package, but just once, thing is, you can re-use it, but you DO need to define it for each package. See http://support.microsoft.com/kb/955709 Btw, SSIS 2012 has an advantage for you in letting to define a project level variable/parameter (e.g. a conn string that is shared among all packages).Arthur My Blog

As Arthur mentioned, you just need to set the package configuration once for each package, so this is a one-off change.http://www.rad.pasfu.com

you can have multiple package configurations and an option to save the configurations .... to a file OR SQL Server (OR other places). I would suggest you to save it to SQL Server as per your requirement. you can write up and save SQL queries to change the connection strings when you want. This is a one time process that you have to do. You have to do it anyways for a package to run in different environments. i.e. you create one configuration file for TEST environment and one for Production. and set up the desired configuration files appropriately.Please mark the post as answered if it answers your question

The caveat with shared data sources is that you need to open each and every package after changes the shared data source for it to be applied to each connection manager. Horrible thing, and completely counter intuitive. SQL Server Configurations work quite well for me: http://www.mssqltips.com/sqlservertip/1405/sql-server-integration-services-ssis-package-configuration/ MCITP BI Developer - MCTS SQL Server (http://bichopsuey.wordpress.com/)