Change from Package connections to Project connections while upgrading packages to SSIS 2012

Migration of SSIS 2008 project to SSIS 2012. All packages have package connections, that need to be replaced by project connections to simplify maintenance. Problem is to go into the design of each and every package and replace every single use in source or destination or in SQL tasks.

Do not change the packages yet with package connections.

Make a backup of each package file!!!

Create all the new project Connections.

Create a new package where you will use all the Project connections, just for reference. A dataflow with source for each connection will suffice. We’ll need this.

Save the package and close it. right-click on it and select <> view code

In the XML code you will find the <connections>

<connections>

  <connection

    refId="Package\Data Flow Task\OLE DB Source.Connections[OleDbConnection]"

    connectionManagerID="{8EFE3DB7-FEF4-4921-8B90-B8A3D730A546}:external"

    connectionManagerRefId="Project.ConnectionManagers[AdventureWorksDW2012]"

    description="The OLE DB runtime connection used to access the database."

    name="OleDbConnection" />

</connections>

Inside a <connection> we find the project connectionManagerID and the connectionManagerRefId

For each Package with package connections, use the editor to find each source or destination that uses the package connection

<connections>

  <connection

    refId="Package\Data Flow Task\OLE DB Source.Connections[OleDbConnection]"

    connectionManagerID="Package.ConnectionManagers[AdventureWorks]"

    connectionManagerRefId="Package.ConnectionManagers[AdventureWorks]"

    description="The OLE DB runtime connection used to access the database."

    name="OleDbConnection" />

</connections>

Replace the connectionManagerID and the connectionManagerRefId by the project connectionManagerID and the connectionManagerRefId.