SQL Server · SSIS

How to “downgrade” SQL Server Integration Services 2014 packages to 2012

The Problem

A few months back, I built an SSIS 2014 project using Visual Studio 2013 that is supposed to serve as a template for future data warehouse projects. Our servers are still in SQL Server 2012, but the upgrade has been approved and so it made sense to create the template project in VS 2013. Until one day, the template was needed for a new project, with the promised SQL Server 2014 upgrade not yet in sight. Since SSDT-BI 2013 is not backward-compatible with SQL Server 2012, I then had to downgrade the SSIS 2014 project so that people can use it using Visual Studio 2012 and deploy it to SQL Server 2012. There are no tools from Microsoft, no StackOverflow forum that gave the perfect answer and mattmasson has kept quiet about the topic. 😦

The Workaround (Summary)

Based on my observation (only  Microsoft can verify), excluding the upgraded components, there are three changes between the XML files of SSDT-BI 2012 and SSDT-BI 2014 dtsx packages.

  1. PackageFormatVersion

SSDT-BI 2012 VS 2012: DTS:Name=”PackageFormatVersion”>6

SSDT-BI 2014 VS 2013: DTS:Name=”PackageFormatVersion”>8

  1. The values used in ExecutableType properties

SSDT-BI 2012 VS 2012: DTS:ExecutableType=”SSIS.Package.3″

SSDT-BI 2014 VS 2013: DTS:ExecutableType=”Microsoft.Package”

  1. The values used in componentClassID attributes

SSDT-BI 2012 VS 2012: componentClassID=”{49928E82-9C4E-49F0-AABE-3812B82707EC}”

SSDT-BI 2014 VS 2013: componentClassID=”Microsoft.DerivedColumn”

Have you guessed the fix already? You got it! It’s only a matter of find (all) and replace! Open the dtsx file in your favorite text editor (mine is  Notepad++) and replace the values used in the DTSX2 Version 2014/01 schema with the ones in DTSX  Version 2012/01 schema. 🙂 Now, there is some “manual labor” required, because you have to manually search for the componentClassIDs and ExecutableTypes in the DTSX  Version 2012/01 schema. Here are the ExecutableTypes and componentClassIDs I encountered so far. Click Replace.xls for an Excel version.

replace

 The Workaround (Step by Step)

  1. If you haven’t done so, open DTSX Version 2012/01 schema
  2. Open the SSDT-BI VS 2013 DTSX package in a text editor
  3. Search for PackageFormatVersion. Change the value from 8 to 6
  4. Replace all instances of Microsoft.Package with SSIS.Package.3
  5. Replace all instances of Microsoft.Pipeline with SSIS.Pipeline.3
  6. Search for all instances of DTS:ExecutableType=”
    unnamed
  7. For each ExecutableType value (Microsoft.ExecuteSQLTask, etc.), go to the DTSX Version 2012/01 schema and search under
    <xs:simpleType name="AnyNonPackageExecutableTypeExecutableTypeEnum">

    for the correct ExecutableType. For example, Microsoft.ExecuteSQLTask in SSIS 2014 should be Microsoft.SqlServer.Dts.Tasks.ExecuteSQLTask.ExecuteSQLTask, Microsoft.SqlServer.SQLTask, Version=11.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc9 in SSIS 2012.

  8. Replace ALL instances (including CreationName attributes) of the ExecutableType value with the one you got from Step 7
  9. Search for all instances of componentClassID=”Microsoft
    componentClassID
  10. For each componentClassID value (Microsoft.DerivedColumn, Microsoft.Lookup, etc.), go to the DTSX Version 2012/01 schema and search under
    <xs:simpleType name="PipelineComponentComponentClassIDEnum">

    for the corresponding GUID.

  11. Replace ALL instances (including CreationName attributes) of the componentClassID value with the one from Step 10.
  12. Now open the package in Visual Studio 2012. If your package does not have upgraded components, then it should open by now. Otherwise, read further.

The Exception

Some components, such as the Script Component in the Data Flow Task, have been upgraded from SSDT-BI 2012 to SSDT-BI 2014. In this case, it’s not only the property / attribute value that is different, but also the structure of the XML node. In this case, replace the Version 2014/01 schema value (e.g. componentClassID=”Microsoft.ManagedComponentHost”)  with any valid DTSX Version 2012/01 componentClassID. Then open the package in VS 2012. The component will be marked as with error, that’s OK. If you have followed all the steps above, your package should at least load the designer by now. Now remove this erroneous component and re-do it in VS 2012 itself.

Conclusion

I  was able downgrade SSIS 2014 to SSIS 2012 packages by modifying the XML file manually. I downgraded 15 packages in 4 hours including finding the workaround described above. It should be faster if you already know what to do. This approach seems more reasonable than re-creating all the packages I designed and developed for weeks with all their DFTs and expressions. No, thank you. Let me know if you encounter any other exceptions or if any of the steps above is not clear.

53 thoughts on “How to “downgrade” SQL Server Integration Services 2014 packages to 2012

    1. Charles, I can’t post the one I mentioned in the blog because the work is for a company and I’m not allowed. I can, however, create a sample tomorrow and update the post.

  1. Thank you! This saved me a lot of time. If you don’t see the component you need in the spreadsheet, you can create a blank 2012 project and add the same component to a new package. Looking at the flat file will give you the properties you need.

    1. Glad to be of help, Bill. Thanks for the suggestion. You are right, that’s easier than searching for the property you need in the schema documentation. 🙂

  2. Hi, what would you do with a foreach container replacement? There seems to be a STOCK:ForEach component that is unchanged between schemas, but we’re getting this error below:

    Error 1 Error loading 1-ImportCSMS.dtsx: Error loading value “<ForEachFileEnu" from node "DTS:ForEachEnumerator". C:\Users\a605337\Documents\Visual Studio 2013\projects\SVOCIS_etl\SVOCIS_etl\1-ImportCSMS.dtsx 1 1

    Any ideas as to how we downgrade the foreach component would be greatly appreciated.

    1. Hi M.S. Dobing, I would need to try that out myself. Right now it’s almost midnight in Singapore. I’ll come back to you if I fond anything within 24 hours. Thanks for reading my post and trying out the solution.

      1. Hi

        Don’t worry I’ve sorted it. I used the solution suggested elsewhere in these comments. I made a blank project and copies the xml from the 2012 version of the for each container there.

        Thanks for this post, it’s an excellent piece of work.

      2. Hi,

        I need help with step no. 6 onwards. I am not able to find and replace those keywords. Could you please elaborate further ?

  3. Hello, thanks for the information, I wonder if is it possible to downgrade an SSIS package from 2012 to 2008 using the same method ?

    1. Hi Victor, I would say it is possible because the schema for SSIS 2008 [https://msdn.microsoft.com/en-us/library/gg587578(v=sql.105).aspx] looks very similar. But I wouldn’t put my money on it because there is significant improvements in SSIS 2012 (e.g. Parameters) from 2008. So you will have to do more than what I did above.

  4. Seems to be very sensitive to something … I have eventually cut and pasted various Execute SQL task templates from simple 2012 templates to get it working… now the Foreach is giving me a problem and the trouble is that I have them embedded deep inside each other so its difficult to keep track of what component is giving the error.

    All in all I got the bulk of the framework across. The only problem is the Foreach loops and what was inside them … still it saved me time and errors .. thanks.

    I have an updated spreadsheet if anyone needs it …

    1. Hi, an updated spreadsheet would be useful!

      I also added three lines:

      Microsoft.FlatFileDestination {8DA75FED-1B7C-407D-B2AD-2B24209CCCA4}
      Microsoft.UnPivot {B8B094A9-8809-4E06-8874-5C55606A9FDF}
      Microsoft.Aggregate {5B201335-B360-485C-BB93-75C34E09B3D3}

      I am currently working also on a similar problem… Did you resolve the problem with the foreach?

  5. Simply awesome. Thanks a bunch for sharing!

    I’ve got fairly big package and converted it successfully.
    First time after I changed them all in Notepad++ and added the package to the newly created project in VS2012, there were still a few but very descriptive error messages which gave me idea what’s going on:
    1. Project connections didn’t work and I had to go back to VS2013, converted them to package connections and repeated the process again
    2. Foreach loop with ADO enumerator is nor even listed on MS DTSX schema (or maybe I missed it somehow), but I came up with idea (probably inspired by your way of thing) and created the same task in VS2012, went to see code behind and voila, SQL2012 XML tag was there which I simply took and used for replacement.
    3. Script Task – you are absolutely right. This wasn’t converted, but I recreated them manually after.

    Basically, I did few iteration after probably missing some tasks at first and in the end got 90% of my package downgraded, but it didn’t take me more than 2 hours to figure all things out.
    On the other hand this definitely helps to understand XML structure of every package.

    1. Hi Verena,

      Thank you for referring this post in your article. My Excel file was indeed incomplete, for it only contained the items in the packages I downgraded. I wrote this post the same day I encountered the issue and didn’t really update it much after that.

      I am glad to see that it is able to help others, and no worries about extending the idea further on your blog. 🙂

      By the way, nice blog. I just moved to Basel so I might try your recipes when I am able to read German. 🙂

      – Vanie

  6. Hi vaniecastro,

    Nice article, thanks for posting it. I am facing issue with one of the custom task. Let me know if any specific changes needs to be done for custom task.
    I am getting following error when run my package

    Description: Cannot create a task from XML for task “PGP Task ver 50”, type “BTWSToolsSSISPGPG.PGP, BTWSToolsSSISPGPG, Version=1.0.0.50, Culture=neutral, PublicKeyToken=ec8c7a0846b7fd30” due to error 0x80070057 “The parameter is incorrect.”

  7. wanted to know similar workaround for VS2015 to VS2013 of SSIS

    because i am using VS2015 to develop ssis packages and deploying it in SQL 2014 integration services version file system option

  8. Many thanks for this, this has worked well for me.

    What I have done, based on the great work you have done here, is to write a simple Windows app to automate the conversion process. I’ve uploaded the program code and instructions to CodePlex, and it can be found here:

    https://ssis2014downgrade.codeplex.com/

    I’ll keep the app updated as and when I can. Many thanks for this great work!!

    1. Thanks a lot for this tool. It worked immediately (after I have set-up an editor macro with still a bug in it) – thanks of course also to Vanie for this great article!

  9. Many thanks for this great work!! This has enabled me to successfully downgrade a package that I needed to get into SSIS 2012 and would have been a pain to have to recreate it.

    What I have done, based on your great work, is to create a simple Windows application to automate the conversion process. I have expanded the number of Components that can be converted and the application seems to work well.

    I’ve uploaded the source code to CodePlex, along with instructions on how to use it. The URL for this at CodePlex is:

    https://ssis2014downgrade.codeplex.com/

    When time allows, I will work on it further to address any issues found, or any new functionality that could be added to it.

    Once again, many thanks for this great work!!

    1. Phil, thanks for the tool! I was able to cut down the time spent downgrading with notepad ++ significantly but your download helped immensely.

      If I have the time one day I may try and add to your work to allow the conversion of FELCs. Thanks again.

  10. Fantastic! My heart sunk when I realised I couldn’t use my 2014 SSIS package on 2012, but your post has saved so much time rewriting. Thank you very much

  11. It is now possible to downgrade a package without needing manual workarounds.
    The current version of SQL Server Data Tools (e.g. Visual Studio 2015 plus SQL Server 2016 Data Tools) allow an SSIS 2014 or SSIS 2016 package to be opened and then downgraded to SSIS 2012 or 2014. In the SSIS project, go to Project Properties. The project and packages will then be re-opened downgraded. At that point, they can be copied and opened in the earlier tools (E.g. Visual Studio 2012 + SQL Server Data Tools 2012).

    1. This absolutely saved me – I had to downgrade a project with a huge ForEach loop and doing so manually was going to be a multi-hour affair. Thank you for posting this comment! I was able to download and install SSDT 2016 and do just this.

      One important thing to note in case this is unclear to anyone else: the TargetServerVersion property is in the Configuration Properties/General area of the project properties window. In addition, this capability does NOT appear in SSDT 2015 even though it uses the VS2015 shell – this option only appears exactly in the fully integraded SSDT 2016.

  12. Thank you so much for this Vanie! It was hugely helpful yesterday when we realized that the VS2013 project we’d build was not going to work when deployed on SQL Server 2012. We had a lot of Script Components so we had to re-do those manually, but this workaround saved us from having to rewrite the entire thing. Cheers!

  13. You know how sods law works – a technical ‘challenge’ arises that you need help with and there’s nobody out there who has encountered a sufficiently similar set of circumstances to yours? Or if they have, they’ve kept it to themselves.
    Well you’ve just broken that law!
    MANY THANKS for posting.

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