Changing the version of an SSIS package.

14

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

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!

14 thoughts on “Changing the version of an SSIS package.

  1. Minor correction, the “ipsec” file you’re referring to is an “.ispac” file. Thank you for the post.

  2. Eliane Egeli says:

    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.

  3. 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! 😀

  4. Marina says:

    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)

  5. Adam says:

    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.

  6. roger patino says:

    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.

  7. Ravi Kishore K says:

    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

Leave a Reply to Kenneth Fisher Cancel 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 )

Google photo

You are commenting using your Google 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 )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

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

Join 2,542 other followers

Follow me on Twitter

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