Search code examples
ssis-2012

SSIS Connection manager

I have created two projects with same connection manager names. Though the names are same, they point to two different environments. I was assuming that if I add package from one project to other I should not face the connection manager related issues. But I still get the error that connection manager "" not found. Does this mean that even if I have same connection manager name, I still need to open each DFT and change the connection managers manually?

Regards, K

Solution

  • I think understand what is being asked here.

    Project 1

    • Project level connection we'll call "TestConnection"
    • SSIS Package we'll call "TestPackage1" is using project level connection "TestConnection"

    Project 2

    • Again, Project level connection we'll call "TestConnection"
    • SSIS Package we'll call "TestPackage2" is using project level connection "TestConnection"

    You move "TestPackage1" from Project 1 to Project 2 and now all data flow, execute sql task are complaining of missing connection manager.

    You asked:

    Does this mean that even if I have same connection manager name, I still need to open each DFT and change the connection managers manually?

    Short answer.... Yes

    Understand that behind the scenes with SSIS packages each object, data flow, execute sql task, sources, destinations, connection managers are assign a unique GUID. That unique GUID is used by each object when referencing each other within the project/solution they were created in.

    So even if you move the package from one project to another and the connection manager was named the same, that connection manager would not have the same GUID from project to project.

    You can see that when look at the code for the connection manager and looking at the code of the package.

    From within Visual Studio:

    • with the package open click on View -> Code from the tool bar
    • Then right click on the connection manager in Solution Explorer and click on View Code

    This will get you a view of the XML code. If I scroll down in my package code to one of the data flows you'll see the connectionManageID is referencing the GUID of the connection manager: enter image description here

    You could, and I have in the past when dealing with large packages, identity the GUID from the project you've copied the package to and GUID the package is referencing then do a find and replace in the code. Save, close all and reopen. I do caution against this as you could easily, completely break the package if not done correctly. This would be completely at your own risk and I'd make sure you have backup copies of everything prior to even attempting it.

    If your package isn't that big, just manually go update each of the tasks.