When SSIS first came out there were huge numbers of jokes and posts about all of the problems and how everyone thought DTS was much better and did they really have to switch? Over the years I’ve come to appreciate SSIS. It is a very powerful and useful tool that can do some amazing things. Certainly the recent versions of SSIS have shown huge improvements. Unfortunately some of the biggest complaints I had back in the beginning I still have today. One of those is just how version specific it is. On the execution side things aren’t too bad. Later versions of dtexec will “temporarily” upgrade a package in order to run it in their version. When editing, however, there is no backward (or forward) compatibility at all. If you open a lower version of SSIS in the tool for a higher version (Data Tools or Business Intelligence Development Studio) then the package is upgraded. Once it’s upgraded there is no going back. The only option to downgrade the package is if you have a backup or an older copy. And you certainly can’t open an SSIS package from a later version than the tool you are opening it with.

This can lead to some problems if you are supporting multiple versions of SSIS. If you have several dozen SSIS packages and you don’t have some sort of naming or placement scheme to make sure you know what version each package is you could easily get into trouble. Let’s say you are helping out a co-worker with their SSIS package. They have SQL 2008 installed and that’s the version of the server where it’s currently running. You open it but forget that you are actually running Data Tools for SQL 2012. You fix their problem but you weren’t paying attention and didn’t notice the “I’ve automatically upgraded your package” message. Your co-worker is thrilled! Until the next day when they let you know they were woken up at 3am because their package just started throwing error messages. Something about the wrong version? Oops. Hope they have a backup somewhere.

There has to be a tag somewhere in the DTSX file that says what version of it, and since SSIS packages are stored in an XML format we should be able to find it pretty easily. If you open the DTSX file in a text editor (or IE, or any other xml viewer) you can find a tag PackageFormatVersion near the top of the package. That property will tell you which version of SSIS this package belongs too. Below I have a nice little table with the PackageFormatVersion for each version of SQL and as a nice bonus which version of Visual Studio each uses as well.

Reference: Interoperability and Coexistence (Integration Services)

Filed under: Microsoft SQL Server, SQLServerPedia Syndication, SSIS Tagged: microsoft sql server, SSIS