Last Wednesday was easily one of the worst days I've had working with SSIS. I've been working since August to stand up a new 2014 data warehouse and have been doing the dev work on a 2012 box while the hardware was racked and all the goodies were installed, and firewalls configured; this means I was using Visual Studio 2010.

After hopping on the new 2014 server I saw that I had VS 2010, VS 2012, and VS 2013 as options: best to stay with the times, right? (Let's not even get into why the versions of Visual Studio don't match the SQL Server versions or the hideous UI of VS 2013 because I can't even).

Here is where I made two critical errors: one from laziness and one from ignorance. I opened my SSIS project that I had created in VS 2010 in VS 2013 and I didn't have a backup. (I'll let you guess which action was stupid and which was lazy). Everything seemed fine. I was able to deploy to the server, although I was still being plagued by that pesky Failed to decrypt protected XML node "PackagePassword" with error 0x8009000B "Key not valid for use in specified state error.

When my scheduled job went to execute the package the following morning it didn't even enter validation. It took a three second glance and said "Nope." I said things not appropriate for mixed company, then found out the server was not 2014, it was actually 2012, and I would need to downgrade my SSIS project.

This is not something Microsoft gave us tools to do. I consulted Twitter and got various responses, ranging from

  • Upgrade to SQL 2014 (tried, it blew up log shipping in a spectacular way)
  • Upgrade just SSIS to 2014 (not possible)
  • Install a side-by-side instance of 2014 (I can see that going very poorly down the road)
  • Use BIML online, open every package, and re-deploy
  • Use Notepad and do a find-and-replace

None of these were going to be fast or easy and I started having chest pain, eyeing a bottle of scotch, and wondering why I hadn't chosen to be a trophy wife. It was then my boyfriend walked in the door, and lawd did he get an earful. After 20 minutes or so of flailing and expletives he asks me, "So, let me get this straight: all you need to do is open a bunch of text files and replace strings?" The answer was a wailed "YES! And it's going to take DAYS" to which he replied "I can write a script that does that in about half an hour."

Stunned silence. Glee. I mentally sent malicious vibes the way of the Visual Studio R&D department and vowed to learn bash and Ruby. Here's how you downgrade a VS 2013 Project like a boss.

  1. Make a zip file of every .dtsx package that needs to be downgraded
  2. Securely transfer it to a Macbook or a laptop that runs Ruby. I'd like emphasize securely here because your packages have server names, table names, logins, and all sorts of other goodies that you should not just email. Encrypt it, spin up an Ubuntu VM, just don't let that go into the wild in plain text.
  3. Find the strings you need to replace. This blog post by Vanessa Rose Castro was my saving grace. She has a downloadable .xls of most of the strings I needed, and she linked to the kb article where I could find the rest. Here is what mine looked like: