Changing the version of an SSIS package

Of all of the annoying parts of SSIS, the major version sensitivity has to be the most annoying. Let’s say you create a package in SSDT 2012. You later open that same package with SSDT 16 without thinking about it. SSDT automatically upgrades your to package version 8 (SQL 2014 & 2016). You run the package and SSDT automatically saves it. And now you can’t open that same package in SSDT 2012. You’re also going to have problems running it on that original SQL 2012 instance.

If you work in a large company with multiple versions of SQL running, and multiple teams creating SSIS packages for those different versions it can be a real mess.

I have a table I try to maintain the different version of packages etc to help out with the confusion.

Fortunately though, one of the big changes (in my mind) of SSDT is the ability to change SSIS versions. Prior to this, you would have to have scripted the package out in BIML and re-created it in the correct version.

So to start with I installed SSDT 17, created a project, and practiced deploying to the SSIS catalog on a 2016 instance. (Yes I wrote 3 posts for no purpose other than to lead up to this one. In fact I’ll probably do one more in a week or two because of this also.)

So starting with the project I created for SQL 2016:

Right click on the project and select properties.

Under Configuration Properties -> TargetServerVersion you’ll see a drop down allowing you to set the version to SQL 2012 to SQL vNext. I’m going to select 2012.

We do get a warning at this point. If anything in our project doesn’t exist in 2012 we will have problems (not surprising, but thanks for the warning).

Next, we build the project.

Now if you take a look at the dtsx file (remember that the ispac file is just a zip file) you’ll see that the packageformatversion is now 6.

Just to be certain let’s import and run it. I know I already created the ispac file but this time I’m going to do the deployment wizard directly from SSDT.Warning: if you are using an ispac file and hand it off to someone using anything lower than SSDT 17 but higher than the version you exported the deployment wizard will upgrade the package when they deploy it which can be a rather unpleasant surprise.

And here it is. FYI You can confirm the package format version by querying SSISDB.internal.packages. Now time to run the package!

Since I had this pointing to my 2016 instance and AdventureWorks2014 I am going to modify those properties of the connection manager.

And Success! We created the package for SQL 2016 then changed the version and deployed and ran it on a SQL 2012 instance!

What SQL version is my SSIS package?