2

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?

Dina Kleper
  • 1,949
  • 4
  • 17
  • 23
  • Was the "HSV" connection also an OLE DB connection? If so, why not keep the connection the same and change connection string using either environment variables (when the package is deployed) or Configuration Manager in SSDT? If "HSV" was not an OLE DB connection are the commands OLE DB compatible? – userfl89 Feb 21 '19 at 14:15
  • Not enough detail; it's unclear to me what you did. – Tab Alleman Feb 21 '19 at 14:39

1 Answers1

1

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.

digital.aaron
  • 5,435
  • 2
  • 24
  • 43