SSIS 2005 changing connection server for "Execute SQL Task" has no effect --

Question

I created SSIS packages a while ago, today I need to re-point them to a different SQL server. I have a bunch of "Execute SQL Task" tasks, which need their Connection property changed to a different server.

I created a new connection manager, double-clicked on each of the sql tasks, changed their Connection property to the new one, ran. It failed, the error was that it could not find the old connection. I restarted the project, none of the SQL Server tasks seem to pick up the new Connection Manager. I opened up the .dtsx files, tried to find the old server name somewhere and manually change it, but I could not find the old server.

What do I have to change to get rid of the old SQL Server and make it connect to the new, correct data source.

I work in VS2005, on Windows 2008 x64 machine (I don't know if it matters or not).

Thank you.

Answer 1

all you had to do is change the connection string in the old connection manager

make a new one if you deleted the old one and in all the tasks select the new one, even if you create a new one with the same name as the old one it won't work because somewhere internal a guid is used

Answer 2

try recompiling after making the changes.

Answer 3

Check to see if package configuration is enabled. The package may still be getting the configuration of the old server from the package configuration.

Answer 4

I've seen this occur before when Package Configurations are enabled. Make sure that this is not the case, recompile, and try again.