CODEWITHSUNDEEP
ssisdatabase-connectionssis-connection-manager
Dina Kleper
Dina Kleper

Reputation: 2061

SSIS - Change between to ConnectionManagers without going through all the components to change its reference

I've used DataSource named HSV then decided to switch to an OLE DB connection with the same name.

I've switched between the two in the ConnectionManagers section.

But now all the components which once referred to the HSV connection have an error icon and in their connection properties there is now a strange Hash like name that I need to click on and choose the updated connection to fix.

In the data flow tasks I need to again choose the tables I want to use/command variables/rewrite scripts...

Is there a way the switch can go more swiftly?

Upvotes: 2

Views: 1486

Answers (1)

digital.aaron
digital.aaron

Reputation: 5707

SSIS uses GUIDs to identify package objects, including connection managers. Even though the IDE usually presents us with a friendly name, all of the relationships between objects in a package are formed on the basis of these GUIDs. So even if you create a new connection that has the same friendly name as an old connection, all your tasks are still looking for the old connection based on its GUID. When it can't find the object by its GUID, you get the validation error icons on your tasks.

The only way this switch can be sped up is if the connections are both OLE DB, then you can change the change the connection manager in the Advanced Editor, by right-clicking on the task and choosing Show Advanced Editor. Then on the first tab of the Advanced Editor dialog, you can choose a different connection manager. When the connection types are the same, SSIS will attempt to re-validate the rest of the task based on the existing configuration. But if the connection types are different, this re-validation will also fail and you'll be stuck recreating the task configuration by hand.

Upvotes: 1

Related Questions