Tag: XML

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

by vaniecastro, posted in SQL Server, SSIS

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.