Migration of SSIS 2008 Package Connections to SSIS 2012 Project Connections
When migrating an existing Data Warehouse from SSIS 2008 to SSIS 2012 you might want to use the Project Deployment Model instead of the Package Deployment Model, because it provides the possibility to use Project Connection Managers which can be used by all SSIS packages in your project. This is very useful when your packages all need to connect to the same databases, because then you only have to configure every Connection Manager once and this configuration is valid for all packages in your project.
The easiest way to do this seems to open a SSIS 2008 package in SSDT 2010 (or newer versions), right click on the Package Connection Manager, choose „Convert to Project Connection“ and do this for every Connection Manager.
This works fine, but only for the first package in the project. As soon as you want to migrate another SSIS 2008 package (I guess that this is usually the case) and reuse the Project Connection Managers that you created during migration of the first package you cannot use the „Convert to Project Connection“ functionality any more. Because when there already exists a Project Connection Manager with the same name then SSDT creates a new Project Connection Manager that you would have to configure separately. The next idea could be to delete the Package Connection Managers, because then the package can access the Project Connection Managers. But this invalidates all your Control Flow Tasks and Data Flow Components, because they all lose their connection. So you have to fix every Task and every Component manually. For a small package with simple functionality and only some Tasks this might be acceptable. But packages that use many Connection Managers and dozens of Tasks or Components would generate high effort for changing every item. And especially when you are not the original developer of the package this could become very tricky. Just imagine that you have to choose the proper connection for every Data Flow Component in a package that looks like this (or is even more complex):
To solve this problem you have to consider that SSIS packages are XML-files, so you can open and edit them with a text editor like e.g. Notepad++ or UltraEdit.
For every Source and Destination component you find a XML tag like this:
Here you need to replace the connectionManagerID with the appropriate ID of the Project Connection Manager and the extra text ":external". For the connectionManagerRefId you need to write „Project“ instead of „Package“.
Repeat this for every Source and Destination in your package. For every Execute SQL Task in your Control Flow you can do something similar. Here you have to change the SQLTask:Connection Property.
Replace it with the ID of the Project Connection Manager:
Next save the package and then delete the Package Connections Managers that you replaced with Project Connection Managers. You can either use SSDT (in this case ignore the error message that states that the Project Connection Manager cannot be accessed because there also exists a Package Connection Manager of the same name) or edit directly in XML and save the package. SSDT should now be able to load the project without any errors. Now try to execute the package:
Et voilà, the package is runnable and uses the Project Connection Managers. There is still one open question: How do I get the ID of the new Project Connection Managers? Again consider that SSIS packages are XML-files, so simply open the Connection Manager files (*.connmgr) in a text editor. Here you can find the ID that you have to use:
One last remark: This is of course only one way to migrate Package to Project Connection Managers, but it is quite simple and it does not cause extra costs like license fee for tools. So depending on the size of your project this could be a good alternative to purchasing external tools.