|||
I’m migrating a Business Intelligence project from SQL Server 2005 to SQL Server 2012. Microsoft has, overall, done a great job with their development and migration tools, and some of the new features of SQL 2012 are great and will save me a lot of time going forward. One neat new feature in SQL Server Integration Services (SSIS) is Project Connections: you can define a connection at the project level, and all packages in the project automatically inherit a reference to that connection.
So this project I’m migrating has maybe 40 packages, many of which had the same two connections (primary source application and the DW database). In SQL Server Data Tools, you can open a package, right-click on a connection, and “Convert to Project Connection.” So far, so good. Problem is, all those other packages that have a connection of the same name will not inherit the project connection because the local one overrides it (by design). And if you open another package and delete the local connection, every task and data flow component that used that connection gets the dreaded red “X” icon–they don’t automatically revert to the project-level connection with the same name. Best I can tell, the only way to fix it in SSDT is to reconfigure every one of those broken tasks and components. The Internet is full of articles showing how to convert a connection in one package, but nothing gave me any clue what to do with the other 39 packages. I couldn’t accept that I would have to do all that–there must be a better way.
At about 2:30AM it hit me: SSIS packages are just XML files.
Now, this post is not a tutorial–if you’re not slapping your forehead and saying “Duh!” then I’m not going to explain it in detail. What I will say is that, when I dug in, I found the files to be readable and consistent. The relevant bit of a package connection looks like this:
connectionManagerID="Package.ConnectionManagers[DatabaseName]"
connectionManagerRefId="Package.ConnectionManagers[DatabaseName]"If you look at a project connection in a package where you’ve already converted it, the corresponding bit looks like this:
connectionManagerID="{0C9FD836-8B6D-4E1F-89E5-7724F0419B6A}:external"
connectionManagerRefId="Project.ConnectionManagers[DatabaseName]"I used Notepad++, but any decent editor should allow you to do a search and replace across several files. While I was at it, I opened each .dtsx file and deleted any <DTS:ConnectionManager> nodes referring to the connections I was trying to convert–remember, if it’s defined in the package, it overrides the project-level connection. Then I opened the project in SSDT–I still had some errors to track down, mainly control flow tasks that just needed to be opened and their connection manager switched from a now-invalid GUID to the new project connection–but it saved me the many hours of tedious work going into each data flow and switching those components. If I had spent more time in the text editor I probably could have figured out how to automate the conversion of the control flow tasks as well, but a small amount of manual cleanup was perfectly reasonable in my case.
So there’s the hint, and I hope it helps someone avoid some frustration, but there are three more things I need to say:
One final note: In principle we could use XSLT for this, but my skills are a little rusty (and I’ve never found a good batch XSLT-applying tool for Windows). If my project had been much larger-scale, it might have been worth the effort.