SSIS 2008: Changing a Data Source's Connection String Not Reflecting in Referencing Packages

Question

Been struggling with this for quite some time so any help is appreciated. Spent a lot of time looking over MSDN, Google, etc, and still no luck.

I have an SSIS project I intent to always execute from within BID. The project has multiple packages, so I created a Data Source to share amongst all the packages.

In each package, I create a Connection Manager based of that Data Source. Now when I go and edit this Data Source's connection string to point to a different server, the change is NOT reflected in the packages.

Now, MSDN says the opposite. In fact, one of the main purposes of Data Sources is to update connection strings easily from within BID; at least according to MSDN.

And for me its not working. This has got to be the most annoying thing I have seen thus far in BID.

Any one have any suggestions?

UPDATE It looks like BID does not synchronize a Data Source change with Connection Managers if the data source's authentication is SQL server. If it is Windows authentication, it detects the change fine.

Very interesting indeed.

Answer 1

Just a guess. This kind of behavior happens when you have a "package configuration" configured.

Right click on any blank area of your control flow and select "package configuration". If the "Enable package configurations" check box is selected and you have a configuration on your connection string, it will override any value you had specified manually.

Answer 2

I figured it out for those that are interested. I am not sure if this is a bug in BID or not but I hope this helps someone else struggling with this problem.

All you have to do is simply open up all your packages at one time. This is as simple as highlighting all the packages in the solution, right clicking, and choose "open".

Once all the packages have been opened, go ahead and edit your Data Source's connection properties. BID will automatically apply the new changes to all the open packages that have a connection manager based on that Data source.

Hope this helps someone.