What SQL version is my SSIS package?
29April 27, 2015 by Kenneth Fisher
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.
| SQL Version | Build # | PackageFormatVersion | Visual Studio Version |
| 2005 | 9 | 2 | 2005 |
| 2008 | 10 | 3 | 2008 |
| 2008 R2 | 10.5 | 3 | 2008 |
| 2012 | 11 | 6 | 2010 or BI 2012 |
| 2014 | 12 | 8 | 2012 CTP2 or 2013 |
| 2016 | 13 | 8 | 2015/2017 |
| 2017 | 14 | 8 | 2017 |
Reference: Interoperability and Coexistence (Integration Services)
Note: This has gotten more traction than I actually expected, and I haven’t been as good about installing and checking the latest versions so if I’m missing a version you need by all means complain in the comments and I’ll look it up, or if you don’t feel like waiting on me (I’m slow sometimes) check it out yourself and mention it and I’ll get it added.
SQL Studies 
So if we have Visual Studio 2010, then we cannot upgrade our SSIS packages to SQL Server 2014?
Remember that the SSIS components of Visual Studio are specialty components that are installed with the SQL Server tools install. To the best of my knowledge (and I’m willing to be proven wrong) you can’t create/upgrade an SSIS package using native Visual Studio.
Hi, i created an SSIS package using SQL Server Data tools for Visual Studio 2012 which transfered data from a SQL 2008 db table to a table on a SQL 2014 database. it ran fine in Data tools, but when i tried to use SQL Agent to run the job i got the following error which leads me to believe there is a compatability issue. i checked the PackageFormatVersion value and this was 6
Executed as user: GDC\ukfhpdbdpe01AGT. Microsoft (R) SQL Server Execute Package Utility Version 12.0.2402.0 for 64-bit Copyright (C) Microsoft Corporation. All rights reserved. Started: 09:33:43 Error: 2015-09-09 09:33:43.86 Code: 0xC0011007 Source: {F2D144A2-9875-4FEE-BBF6-4C461DD91BAF} Description: Unable to load the package as XML because of package does not have a valid XML format. A specific XML parser error will be posted. End Error Error: 2015-09-09 09:33:43.86 Code: 0xC0011002 Source: {F2D144A2-9875-4FEE-BBF6-4C461DD91BAF} Description: Failed to open package file “C:\Users\southwm\Documents\SQL Server Management Studio\BulkTransfer\BulkTransfer\Package.dtsx” due to error 0x80070003 “The system cannot find the path specified.”. This occurs when loading a package and the file cannot be opened or loaded correctly into the XML document. This can be the result of specifying an incorrect file
I just wondered if you may be able to suggest anything
Thanks
The important parts of the error are:
Executed as user: GDC\ukfhpdbdpe01AGT
and
Failed to open package file “….” due to error 0x80070003 “The system cannot find the path specified
Basically what that means is that that user doesn’t have access to the .dtsx file. If that’s not the account you think you should be using then you can look there if it is then you just need to get it granted read permissions to that file/directory. (I don’t think it needs write permissions but I could be wrong.)
After installing SSDT for VS2015 latest released on 4th Feb 2016. it got installed properly and BI template added in VS 2015 for new project.
after creating NEW SSIS project, it gave me error “The located assemblys manifest definition does not match the assembly reference. (Exception from HRESULT: 0x80131040)” SSIS package is not opening.
I’m honestly not sure. I suspect it’s a problem with multiple versions of the tool being installed and your path variable is telling it to look in the wrong one first but I can’t be sure. I would suggest posting this on one of the forums dba.stackexchange or sqlservercentral.com to get the best answers.
I have seen that a few times. Uninstalling the SSDT 2015, reboot and reinstalling fixed it every time we saw this.
Used this today 🙂 thx
Yep. Comes in handy from time to time. I’ll be adding 2016 soon.
Yeah true. Actually dealing with the ‘To run a SSIS package outside of SQL Server Data Tools you must install’ error right now…never had to deal with before. Our uat box does not have is svc running, and prod does. It’s erroring in uat.
SQL 2016 supposed to handle any SSIS pkg, or so I’ve read. That’d be nice. Wonder what upgrade paths are available, or if it’s a straight shot only to SSIS 2016? 😐
I had a package in SSIS with LastModifiedProductVersion of 10.50.6000.34 from production. I then worked on it and it saved it and it made LastModifiedProductVersion 10.50.1600.1. I am using 2008 Visual Studio and SQL Server 2008r2. So I updated the SQL Server with the service pack 3. I then rebuilt my package but the LastModifiedProductVersion is still 10.50.1600.. How can I make it the same as it was?
Sorry for the delay answering. Honestly I wouldn’t worry about it. It shouldn’t affect your usage at all. My understanding is that packageformatversion is all that matters. That said remember that a dtsx file is nothing more than an XML file. Editing it is pretty easy.
If you get into a version issue this tool could be handy. Does not work in all cases but saved my butt a few times recently. https://ssis2014downgrade.codeplex.com/
From what I understand BIML is also useful solution. You can convert into BIML then back to any SSIS version you choose.
How can I do this in bulk? Let’s say that I have a folder of about 100 DTSX packages. Let’s also assume that I don’t have BIDS or SSDT installed. Are there any 3rd party tools that can crawl a folder of SSIS package and tell me what SQL version they all are?
Not that I know of. You could write some PoSH code to traverse the XML but that’s probably the easiest method I can think of.
I have posted a question on DBA Stack Exchange seeking help.
http://dba.stackexchange.com/questions/162133/
[…] This driver not only works with Oracle, but also with Teradata. Choose the version based on the version of SSIS you are […]
Useful info! However, I had focused on the CreationName instead of the PackageFormatVersion.
For future reference, CreationName “Microsoft.Package” refers to “PackageFormatVersion” value 8; “MSDTS.Package.1” = 2; “SSIS.Package.2” = 3, “SSIS.Package.3” = 6
Cool 🙂 I have to admit I haven’t looked that closely at the vast majority of the tags in SSIS. There are certainly plenty of them.
Thanks!
Also noticing that an install of SQL Server 2014 generates some standard SSIS packages in the msdb database: “TSQLQueryCollect”, “SqlTraceCollect”, “PerfCountersUpload”, etc.
They have a PackageFormatVersion of 7 with a CreationName of “SSIS.Package.4” . I’m not sure which version of Visual Studio these reflect.
[…] I have a table I try to maintain the different version of packages etc to help out with the confusio… […]
FYI the PackageFormatVersion is still 8 for SQL Server 2016 and SQL Server 2017 both with Visual Studio 2017.
Oh you are wonderful. I keep meaning to check on both of those and and keep getting distracted.
How can I read the PackageFormatVersion property of an encrypted older SSIS package? Sure I can decrypt and open it in my VS but the package gets upgraded in the process so that the value of the PackageFormatVersion property would reflect the new version after the on-the-fly upgrade and no longer the original value.
Unfortunately if it’s encrypted you have to decrypt it before you can do much. You could try getting older versions of VS until it doesn’t do an upgrade?
[…] SQL Studies – What SQL version is my SSIS package? (this link contains the table writen below) […]
[…] SQL Studies – What SQL version is my SSIS package? (this link contains the table writen below) […]