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.
-
PackageFormatVersion
SSDT-BI 2012 VS 2012: DTS:Name=”PackageFormatVersion”>6
SSDT-BI 2014 VS 2013: DTS:Name=”PackageFormatVersion”>8
-
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”
-
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.
The Workaround (Step by Step)
- If you haven’t done so, open DTSX Version 2012/01 schema
- Open the SSDT-BI VS 2013 DTSX package in a text editor
- Search for PackageFormatVersion. Change the value from 8 to 6
- Replace all instances of Microsoft.Package with SSIS.Package.3
- Replace all instances of Microsoft.Pipeline with SSIS.Pipeline.3
- Search for all instances of DTS:ExecutableType=”
- 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.
- Replace ALL instances (including CreationName attributes) of the ExecutableType value with the one you got from Step 7
- Search for all instances of componentClassID=”Microsoft
- 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.
- Replace ALL instances (including CreationName attributes) of the componentClassID value with the one from Step 10.
- 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.
Vanie's View 
Thank you for posting this workaround. I was able to successfully downgrade a lengthy SSIS package, without any rewrites.
You are most welcome geordon. I’m glad to know the workaround has been useful to at least one other person.
It worked for me by going to VS Project->Properties and then change to the appropriate version, say 2012, that you need. The package has converted successfully from VS2015 to run on 2012.
Can you show the difference between your package before and after the manipulation please?
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.
It’ll be ok, I found my mistake ! Thank a lot for posting this workaround!
Great to hear that, Charles! You are most welcome! 🙂
Thank you for the post, it helped me too!
You’re so much welcome 🙂
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.
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. 🙂
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.
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.
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.
Glad to be of help 🙂 Thank you for the nice words.
Hi,
I need help with step no. 6 onwards. I am not able to find and replace those keywords. Could you please elaborate further ?
Hello, thanks for the information, I wonder if is it possible to downgrade an SSIS package from 2012 to 2008 using the same method ?
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.
OMG Total God Send
You just saved me several hours of work. Thank you!
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 …
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?
basing on mentioned in this article I created for me simple commandline file converter (it replaces all mentioned above). ForEach statement need to be corrected manualy 😦
SSISDowngradeVersion.zip
Hi w8Cat,
I didn’t notice before that there was an unapproved comment. So sorry. Thanks for your effort to create the commandline converter.
What about if you have to change the package? Would you have to do this all over again? Or just replace the differences?
I’m not sure I understand what you mean Caio..
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.
Thanks for the additional information MikeSQL!
Hi,
thank you very much for this great blog post, it helped me a lot. Also you Excel file was very useful.
I downgraded my packages based on your approach but with a few differences and I updated the Excel file with a few missing components: http://blog.haunschmid.name/downgrading-an-etl-project-from-ssis-2014-to-ssis-2012/ Just wanted to let you know that I mentioned your very helpful blog post.
Best regards,
Verena
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
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.”
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
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!!
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!
Many thanks Phil (and Vanie)
The app worked great for me
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!!
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.
Great tip!!! It helped me a lot. Thank you for sharing.
It worked…I used the app suggested and then had to make some changes.
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
This is a very helpful post. Thank you very much for sharing! It saved me a lot of hours…
Your effort help us so much. Thank you for help and post!!!
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).
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.
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!
Fantastic piece of work. it saved me a ton of time.
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.
do you have workaround steps for downgrading from 2014 to 2008 R2. Please send. i am stuck.
Great job with this… Saved my tail from having to re-create a package…
How and where do you find these files to be edited?
Big help, thanks!
I found another easier way to do the same . Simply right click on the project . select >properties>General>TargetServerVersion change it to SQL 2012. I worked for me .
Just so everyone knows, you can now do the conversion between 2012, 2014 and 2016 SSIS packages in the 2016 SSDT-BI. Right click on the project and select the target version. As long as you don’t use any features that are not supported by the older version studio will do the upgrade or DOWNGRADE for you now.
I am facing below error which downgrade ssis package(KingsWaySoft Component) from 2014 to 2012
Error 2 Validation error. Data Flow Task: Data Flow Task: The component metadata for “Dynamics CRM Source, clsid {874F7595-FB5F-40FF-96AF-FBFF8250E3EF}” could not be upgraded to the newer version of the component. The PerformUpgrade method failed. Package.dtsx 0 0
Could you please let me know to fix it.
To those who said thanks – you are welcome. I am glad to be of help at a time when this was one of the few solutions available. To those who commented over a year ago but I didn’t managed to reply to or even approve your comment – I am sorry. The past two years have been different than the years before them, so I completely ditched this blog. Sadly, I haven’t worked with SSIS for a while as well (I did other interesting things in between instead) and it seems from some of the comments that Microsoft finally provided the solution. yay!