Downgrading an ETL project from SSIS 2014 to SSIS 2012

Recently I came into the situation that I had a SSIS package built with Visual Studio 2013 and SQL Server Data Tools (SSDT) 2014 which should be deployed to SQL Server 2012. Running the packages from Visual Studio 2013 and inserting data in a database on SQL Server 2012 worked, it also worked to deploy the packages to the server, but when trying to execute a package directly on the server, an error message appears:

The version number in the package is not valid. The version number cannot be greater than current version number.

The problem is that the PackageFormatVersion of SSIS packages created in VS 2013 is 8, but SQL Server 2012 requires it to be 6. Just changing the value does not trick the SQL Server unfortunately 😉 One could have known that because it is reported in the MSDN Blog about SQL Server Data Tools – Business Intelligence for Visual Studio 2013 (SSDT BI) but one can not read and know everything 😉

Other people have the same problem:

• Deploy VS2013 SSIS packages to SQL Server 2012 & edit in VS2012 | stackoverflow.com
• SSDT BI 2014 backward compatibility for SSIS 2012 | connect.microsoft.com

But no real answer can be found. I tried several things:

• Someone gave me the idea to convert my packages to BIML (Business Intelligence Markup Language), adapt the files (if necessary) and import them in VS 2012. The same source also told me that this would be possible with the BIDS Helper. I installed the tool but I couldn’t find an option to convert packages to BIML files and I also couldn’t find anything online about it. By coincidence I stumbled over the tool BIML Online, which was only only for 6 days at the time I looked. Unfortunately it’s only Version 0.1 and it only worked for parts of my project and didn’t convert the packages at all. The tool looks cool and very professional though, I guess it will just take some time until it’s fully usable 🙂
• Visual Studio Project Converter: I gave it a shot although I was not very convinced from the start. A solution can be converted manually very easily and just converting the solution is not going to help when the source code is in the wrong format as well…
• SSIS Downgrade: This tool sounded more promising but unfortunately they stopped development after SQL Server 2008 …

Those attempts all failed. The closest I came to a converted project was by following the instruction in this blog: How to “downgrade SQL Server Integration Services 2014 packages to 2012 | vaniecastro.com. Unfortunately the provied Excel Sheet (which is very helpful!) is not complete and also how to deal with foreach loop containers is not explained. The blog suggests to look up missing things in the XML schemas (I’ll explain that below) but it’s cumbersome and it seems not to contain everything. So at first I thought this approach would also fail and deleted my half-converted project (it was broken from all the trial and error anyway) but today I gave it another try. The approach in the blog post is simply to replace everything by its previous value/name/… (whatever you like to call it). My approach is a little different but based on this and I would not have managed to do it without this post.

The task

In this post I explain how I managed to downgrade 3 SSIS packages that have several different tasks and also contain two kinds of Foreach Loop Containers (Foreach File Iterator and Foreach NodeList Iterator). I also extended the Excel sheet by vaniecastro.com.

Prearrangements

You need VS 2012 and VS 2013 for this.

I copied the folder containing my VS 13 solution and all files to the same location:

• ssis_project
• ssis_project_12

Open the .sln file in a text editor and modify the first few line such that it looks like this:

Microsoft Visual Studio Solution File, Format Version 12.00
# Visual Studio 2012
VisualStudioVersion = 11.00

Don’t be confused, the internal version number (VisualStudioVersion) is not the same as the one in the name VisualStudio 20xx. Here is a list if you are interested.

My original Solution File also contained a MinimumVisualStudioVersion, I just removed that and it didn’t make any problems.

Download the Excel File

Creating the packages

Delete all package files from your ssis_project_12 folder.

Open the solution in Visual Studio 2012 (I had to right click and choose VS 2012, otherwise it would open it in VS 2013). You should see the same as in VS 2013 except for the packages. If you have Project.params and Connection Managers at project level they should still be there.

On my second screen I opened the original solution in Visual Studio 2013 (for this task it is very helpful to have two screens or a very big one :)).

Create new packages in your project ssis_project_12 with the same names as in the old package.

For the package you want to start with open the code view (Right click > View Code) in both projects.

The first package

Copy and paste

...

and

...

For me this worked without any problems (to check if the XML is correct just open it in the Design View -> double click on the package).

I didn’t want to rush anything, so I just copied the first task, and copy pasted everything from the Excel file (e.g. “Microsoft.Pipeline” has to be replaced by “SSIS.Pipeline.3”). The blog post tells us to search for instances of “DTS:ExecutableType” and all instances of “componentClassID=” but I found it more convenient to search for each value that is in the second column and replace it by the value in the third column. This worked out perfectly, so I went on with the second task in the Control Flow and so on (I always opened the Design View in between, then it tells you if something is wrong).

All my packages have similar structure: A few tasks that extract / transform data and then a Foreach Loop Container that performs some more transformations. So after the first few tasks that were easily converted I had to add a Foreach Loop Container using the Design Mode. Add all the settings manually.

The next step was to add the task thats were inside the Foreach Loop Container. This can be done in the same fashion as above. Don’t forget to replace everything according to the Excel file.

Problems occured with the Script Component and the FileSystemTask. Although I found some older “names” for the FileSystemTask ({BA17E6A0-5855-4145-9847-3A1E64479383}, {7D1024E3-A8A8-4D67-A06C-E17EA5E64498}) none of them worked for my version, so I deleted the node and readded it manually. For the Script Component I decided immediately to redo it manually because I read at several places online that this has changed and can not be downgraded manually. In my packages the Script Component was only ever followed by a FlatFileDestination, so I decided to delete both and create them later manually (search the XML file for “script component” and delete everything that contains it – in my case the tags were component, inputColumn and path). This is probably annoying if there are more nodes afterwards, but it could also work to rewrite the code (please let me know if you manage to do so).

Modifying the first package took me about 4 hours. The second and third together took me about 3 hours because than I already knew most things I had to be careful about.

What if something is not in your Excel file?

In this case, vaniecastro.com suggests to look the values up in

• DTSX2 Schema for Version 2012/01
• DTSX2 Schema for Version 2014/01

but as mentioned above it does not contain everything and for me it took a long time to search.

I followed two different approaches:

1. Add a component like “Microsoft.Sort” to a package that could be opened (if you don’t have one yet just create an empty one), look into the code and use the value provided there. All components I modified that way were added to the above Excel File.
2. If that didn’t work (might be that the structure also changed and not just the name), I had to redo all items of that type manually.

Don’t forget to delete those components afterwards! 😉

Summary: Convert a package

This section got longer than expected and maybe too confusing, this is the short version:

1. Create a new, empty package
2. Copy variables and connection managers
3. Copy all tasks, that are not inside a Foreach Loop Container. Remove Script Components, adapt XML tags according to the Excel Sheet. Manually add Script Components, copy code from the other project.
4. Create an empty Foreach Loop Container, manually edit the options
5. Copy all tasks, that belong into the Foreach Loop Container. Proceed like in step 3 (in case you have nested Foreach Loop Container, nesting this process should work as well)

Things to do manually

Some thing definitely have to be recreated manually (otherwise please let me know!):

• Foreach Loop Container
• FileSystemTask
• Script Compoment
• Positioning: Some Data Flow tasks lost their position, but maybe I copied them sloppily

One thing that can be done manually but also by copying, is setting Precedence constraints (arrows between task in the Control Flow). Since I don’t have any special constraints I decided it would be quicker for me to just do it in the Design View but if you want to copy the XML code, that’s also fine.

Final words

There is one thing I wonder about: When it is possible to execute such packages from within VS 2013 and also to deploy them, why is it not possible to execute the deployed packages?

The only good thing I see in this is that you learn a lot about the XML structure of SSIS.

If you find anything missing in the Excel file just let me know (you can also add a comment on the other blog but I am not sure if she’s still updating the Excel Sheet).