Changing the version of an SSIS package.
22August 23, 2017 by Kenneth Fisher
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.
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!
SQL Studies 
Minor correction, the “ipsec” file you’re referring to is an “.ispac” file. Thank you for the post.
Thanks! I’ll get it corrected.
Thank you for the article you saved me a lot of time. With changing to the correct Server-Version all packages worked above all the script components.
Glad to hear it! I was thrilled to learn this one myself.
Had a brief hope this would fix my issue…alas and alack, it did not. A 2017 built dtsx file rebuilt to 2016 to run on a 2016 box still fails with the same XML error. Can’t back it any further (2012) because previous version don’t appear to offer the OData connectors my project needs.
Guess I’m gonna re-do the entire thing as a 2016 build from the get go and see how that works.
Have you checked to see if it’s something simple like having encrypt all with user key?
Actually, it turned out to be a difference between the login account on the computer I was building the SSIS package on (and testing with) and the SQL Server User Agent account that ran the completed package insofar as access to the source data went. Once we figured that out the agent ran fine.
So, your solution likely works fine…and it was an identity mismatch in my case that was tripping everything up. Still, great tip and, for a brief time, it gave me some hope in an otherwise hopeless day! 😀
When I want to save the package.dtsx for TargetServerVersion SQL Server 2014
I get an error :
Failure saving package. (Microsoft Visual Studio)
Additional information: An invalid character was found in text content(msxm16.dll)
I found this:
https://stackoverflow.com/questions/52819127/ssis-package-save-error-an-invalid-character-was-found-in-text-content-msxml6
There isn’t an answer but the comments looked helpful.
We have a product that uses SSIS and the issue I’m facing is that we have to be able to support multiple target SSIS versions. What I really want to do is to automate this process on the build server so as to avoid duplication of storing the packages with different target versions under source control. Alas no joy for now.
I’m afraid I don’t know of any way to do that via command line. Depending on the number of versions you are working with though, and what options, you could always keep all of your packages at a given baseline. Then, if the version of dtexec is higher than the version of the package it will automatically do a temporary upgrade. You’d need to do testing on your end of course to make sure everything works ok doing it that way.
I had to modify a functional package which I created using the SQL Server Import/Export wizard in SQL Management Studio. I would export it, modify the code using the SSDT package in Visual Studio, and then import it, only for it to error out. I couldn’t find the version setting that the author specified.
What I had to do was create a new SSIS project in visual studio, “add existing package” to the project, save it with the correct version, and then open up the file location and manually get the ssis file to import back into the SQL server.
Yea, I only noticed recently that it appears to be project setting not a package setting.
When I am trying to upgrade to 2017 from 2014 but its gives the below error:
Error: 2019-09-26 07:06:06.99
Code: 0x00000003
Source: Accumulate Error Messages
Description: The Script Task is corrupted.
End Error
Error: 2019-09-26 07:06:06.99
Code: 0xC0024107
Source: Accumulate Error Messages
Description: There were errors during task validation.
End Error
Hello,
When i try to downgrade my package from 2019 to 2012 target sql server version, my script task code gets completely wiped out. And i am facing issues manually changing the code.
Is there a way to resolve this?
Regards
As a general rule I recommend posting questions like these on a forum. There are people out there far better at debugging these kinds of things than I am. I like dba.stackexchange.com and sqlservercentral.com.
However, at a guess I’d say you are going to have to copy out the code and put it back after the downgrade. I’ve had a fair amount of troubles over the years with automated processes upgrading/downgrading packages and the script tasks.
Hi Kenneth,
I created a package in my workstation using Visual Studio 2010 for SQL Server 12 (v 11.0.2100.60) with a format version 6 (I can check it in the DTSX file), but when I later deploy it on a SQL Server 12 (v 11.0.7462.6) using the SQL Server Import/Export wizard in SQL Management Studio, somehow it gets upgraded to format version 8. So I can’t execute it because I got the “Package migration from version 8 to version 6 failed with error 0xC001700A “The version number in the package is not valid. The version number cannot be greater than current version number.”. How can it automatically upgrade the package to a version it can’t manage? Is there a way to solve it?
Thanks a lot!
Thank you very much, the thing with the Configuration SQL-Server 2019 to SQl-Server 2016 solved all my problems
Fantastic! Glad I could help.
SQL Version Build # PackageFormatVersion Visual Studio Version Compatibility Level Support End Extended Support End
2019 15 8 2019 150 1/7/2025 1/8/2030
2017 14 8 2017 140 10/11/2022 10/12/2027
2016 13 8 2015 130 7/13/2021 7/14/2026
2014 12 8 2012 CTP2 or 2013 120 7/9/2019 7/9/2024
2012 11 6 2010 or BI 2012 110 7/11/2017 7/12/2022
2008 R2 10.5 3 2008 105 7/8/2014 7/9/2019
2008 10 3 2008 100 7/8/2014 7/9/2019
2005 9 2 2005 90 4/12/2011 4/9/2013
Thank you, troubled my mind due to an old SSIS package and you helped.
Cheers
Glad to hear it!