What SQL version is my SSIS package?

19

April 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

Reference: Interoperability and Coexistence (Integration Services)

19 thoughts on “What SQL version is my SSIS package?

  1. avi says:

    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.

  2. Mark Southwell says:

    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.)

  3. d says:

    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.

    • Keith Mescha says:

      I have seen that a few times. Uninstalling the SSDT 2015, reboot and reinstalling fixed it every time we saw this.

  4. Lee Everest says:

    Used this today 🙂 thx

  5. Darren Wood says:

    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.

  6. kmescha says:

    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/

  7. John G Hohengarten says:

    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?

  8. […] This driver not only works with Oracle, but also with Teradata. Choose the version based on the version of SSIS you are […]

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Enter your email address to follow this blog and receive notifications of new posts by email.

Join 1,614 other followers

Follow me on Twitter

ToadWorld Pro of the Month November 2013
%d bloggers like this: