Errata
Important, please read!
Hi, I noticed something weird when upgrading my packages as described below, and thought it would be fair to share this with you.
Script Tasks might not get upgraded well
There seems to be an issue with upgrading packages with Script Tasks in it. The packages I upgraded with just plain SSIS stuff did not have that problem.
The following warnings are given:
Warning 0x40016044: DFT_Export_Person: Found SQL Server Integration Services 2012 Script Component SCR_Add_RowNumber that requires migration!
Warning 0x40016044: SCR_Foo_Bar: Found SQL Server Integration Services 2012 Script Task “ST_a1fbd3cd5d924758a2c98397289876d0” that requires migration!
However, the wizard still says that the upgrade is successful:
The message that the upgrade is successful
I think these warnings should be big fat errors and I will show you why.
Before the upgrade: the script task has a ReadOnlyVariable and a ReadWriteVariable
Before the upgrade: the script task has code in public void Main()
After the upgrade: the script task does not have the ReadOnlyVariable nor the ReadWriteVariable
After the upgrade: the code in public void Main() has disappeared
As you know I am not anti-Microsoft, in the past I have thanked them for being so cool towards developers.
But as far as this upgrade process is concerned I wonder: what must the developer at Microsoft have thought when making this a warning instead of an error? Was it monday morning, was he or she drunk or on drugs? I don’t know, the package is now cripple and will not work. That is not a warning.
So I “warn” you, pay attention to your script tasks, if you use them.
Use “Upgrade All Packages” option
After upgrading, my builds started to take a lot of time (up to 4 minutes, I suspect an on-the-fly upgrade was being done). While investigating this, I found that in the project file all packages are also listed with a PackageFormatVersion per package in it, which was still 6!
I am not sure if this is a Visual Studio bug, if the PackageFormatVersion of the dtsx file says 8, while the dtproj file says 6 for the same package.
Anyway, after I modified the procedure slightly, my builds were fast again (20 seconds instead of 4 minutes).
To achieve this, I opened the SSIS 2012 project using Visual Studio 2015, then chose Upgrade All Packages from the contextmenu.
After that I changed the TargetServerVersion as described below.
Sorry for any inconveniece caused by my initial article, and I hope this update will help.
(The original post starts below)
Introduction
If you are intending upgrading your SQL Server installations to 2016, you’ll probably want to do the same with your SSIS packages.
And I can tell you, coming from SQL Server 2012 or 2014 this is fairly easy to do.
But with my experience I want to help you doing this not the wrong way.
Problem
I made the mistake by using the SSIS Package Upgrade Wizard while the projectversion still was SQL Server 2012.
Funny thing is that the packages seemed to upgrade, however I got all kinds of errors and they just did not work.
Solution
While the solution seemed so easy.
For this article I prepared a SSIS project made with SQL Server 2012.
In the picture below you can how it looks like when opened with SQL Server Data Tools 2015 on a Server with SQL Server 2016 installed.
After the project name, between brackets you can read that this is a SSIS 2012 project.
Also, if you open a package in Notepad (be careful not to change it) you can see that the PackageFormatVersion is 6. This means SQL Server 2012.
The simplest way to upgrade all packages in the project is to change the version of the SSIS project! Please be sure to backup your entire project and all SSIS packages first.
To upgrade all packages, right click on the project node (in the picture on SSIS-E09-Packages (SQL Server 2012) and select Properties.
Then select Configuration Properties and change the TargetServerVersion to SQL Server 2016.
Changing the TargetServerVersion ..
Then read the message, follow the instructions and click Yes.
Upgrade instructions and warning
The good news is, that’s basicly it! When you open a package with Notepad again, you can see that it is upgraded to PackageFormatVersion 8. This means SQL Server 2016.
Package which is upgraded to PackageFormatVersion 8 (SQL Server 2016).
In my experience with more complex packages than used for this demo, they all upgraded with no problems. I used all the standard SSIS components. Script Tasks and Script Components did not have to be recompiled.
Still a warning about the Upgrade All Packages menu item, available from the SSIS Packages node. You don’t need it if you simply change the TargetServerVersion of the project as just described. If you use Upgrade All Packages with the wrong TargetServerVersion it seems to work, but the output packages do not work.
The Upgrade All Packages menu-item
Conclusion / Wrap up
When you do it the right way, upgrading SSIS Packages from 2012 to 2016 is easy to do. However I did not test this with 2014, I am quite sure it will work in the exact same way.
As you might have noticed I have changed the frequency of my blog posts, due to several circumstances. I still try to do 2 or 3 a month.
Last updated: 27 November, 2016.
(c) 2016 hansmichiels.com – Do not steal the contents – spread the link instead – thank you.
I am working on upgrade our sql server to 2016, and I have meet many problems. This is really help me , but I did not find TargetServerVersion, I opened project property pages, but did not see it. I am a fresher to do the SSIS.
Thanks for your help.
Hi Cyndi, first make sure you use the latest version of SQL Server Data Tools (for Visual Studio 2015), in my previous post you can find the download link:
http://www.hansmichiels.com/2016/12/10/quiet-install-of-sql-server-sp1-all-tools-sql-server-series/
Then you should find the property pages when you right click on the project node and select Properties.
Then select Configuration Properties and change the TargetServerVersion to SQL Server 2016.
Hope this helps.
Hi Hansel,
nice article indeed. I’ve some queries below:
# I’ve successfully upgraded one package from VS 2010 to VS 2015 and now I checked in code and the PackageFormatVersion showing=8
# When I executed the package it works fine but the issues are:
# When I executed the package from the command prompt by
dtexec.exe /F Package1.dtsx
The version number in the package is not valid
The version number cannot be greater than the current version number
Please suggest me a possible solution as I’ve used Windows task scheduler for all package jobs scheduling but in this new version the execution fails from command prompt.
Thanks
Hi Mohammed,
If you still have SQL Server 2012 installed on the same machine, you might be calling the SQL Server 2012 version of dtexec.exe
Change the command to include full path of (the SQL Server 2016 version of) dtexec.exe surrounded by double quotes, it’s in the shared features folder of SQL Server 2016.
It depends on your installation what the exact path is, for a default installation it would be
“C:\Program Files\Microsoft SQL Server\130\DTS\Binn\dtexec.exe” /F Package1.dtsx
Let me know if this solves your problem, otherwise I will think along with you what else could be wrong.
Hi,
I have an SQL Server 2012 installed and Visual Studio 2015. I have few SSIS packages developed in 2008. I have upgraded them to 2010 and now trying to upgrade them to 2015 Visual Studio. When i paste all 2008 packages under SSIS Packages in SSIS project and run “Upgrade all packages” I get below erorrs and all upgrades fail.
TITLE: The Upgrade had Errors
——————————
Error 0xc001f429: Package Upgrade: Object reference not set to an instance of an object.
——————————
BUTTONS:
OK
——————————
Please help me
Hello Sai, sorry to hear that your SSIS Packages upgrade failed. It is however for me hard to tell what could be the cause. You said you upgraded to 2010 but 2010 is not a SQL Server version, do you mean Visual Studio 2010? Maybe then just the Visual Studio project is upgraded, but not the packages itself, maybe they are still SSIS 2008. I never had to upgrade packages from 2008 to a higher version. I know that with BimlOnline you can reverse engineer SSIS Packages. Maybe you can convert the packages to Biml, and then from Biml to a newer SSIS version.
See this blog post about it:
https://www.solisyon.de/reverse-engineering-a-dtsx-into-biml-with-bimlonline/
Hope this helps.
Best regards,
Hans
Hi,
After upgrading the ssis package from VS2013 to VS2015 with SQL server 2016, Package is getting failed which is having the Script task, even though i have modified the script task configuration properties to SQL server 2016. The error iam getting is
“Foreach Loop Container:Error: The GetEnumerator method of the ForEach Enumerator has failed with error 0x80004005 “Unspecified error”. This occurs when the ForEach Enumerator cannot enumerate.
“
Thanks my friend!
Most of problems upgrading / downgrading can be solved by this method. Thx!!
Nice to hear, Ruben. I am glad that this article was useful for you.
I converted a project from SSIS 2014 to SSIS 2016. It worked fine when I ran it in VS2015 BIDS, but the Script Tasks are simply ignored in test and production servers. Changing back to 2014 fixes it. But I kind of wanted to use SSIS 2016.
Is there something I have to do to make it work in SSIS 2016
Hello Bob, do you mean the script tasks actually contain the original script exactly as in the SSIS 2014 package and are still not working? Have you checked the scripts that they are not “empty” (as discussed in my blog post)? If they contain the scripts, this is new for me. I really start to regret that I blogged about this subject and thought is was easy. Upgrading seems cumbersome and buggy. A few things you can try: If you have only a few packages add a new Script Task in SSIS 2016, copy the code from the SSIS 2014 Script Task in it, and let it replace the SSIS 2014 Script Task. So just stupid manual upgrade. The second thing you can try is go to http://www.bimlonline.com, then import your SSIS 2014 packages, and once you have the biml version of those packages, generate SSIS 2016 packages from the packages in biml format. I must admit I did not try this myself, but you could. Hope this helps. Best regards, Hans
Thanks for your help. I found my problem. The build team was rebuilding my packages with an older version of msbuild, (or something else they did not divulge to me.) It would reset, or clear the code from my script task, thus the ST did nothing. When they upgraded the build server, all worked well again.
Hello Bob, good to hear that you found it, and thanks for letting me know. Best regards, Hans
Thanks its very nice article! In another note I have quick question. I’m working now with project upgrading from SQL 2012 to SQL2016. Our DBA did upgrade of SQL2016 and hand over to me for SSIS upgrade but I encountered a lot of issues. This first simple one I can’t do import SSIS packages from IS catalogs using vs2015, issues I’m not able to see the local server to choose even if I’m in the SSIS server box. After digging the possible issues I have see that two instance of SSIS services are running SSIS 11.0 and SSIS 13.0 version which still available SQL2012 version this is my possible guess the problem. I didn’t see it the reason behind keeping the old version rather directly upgrade to 2016. Also another problem I face is the I use to use Attunity Oracle driver for pulling data from Oracle server even if I install the version 4.0 from Microsoft that’s compatibility with SSIS2016 I’m not able to see the Oracle Datasource and Oracle Destination under DataFlow. Do you have any clue you can think of? Is this necessary to keep the old version of SSIS2012 server even if the SQL Database has only one instance which upgraded to SQL2016? Thanks for you time. Daniel
Hello Daniel,
A lot of information you give and a lot can have gone wrong.
If I understand you correctly:
– you want to get your packages out of the IS Catalog. Why would you want to do that? Don’t you have the SSIS Packages in a Visual Studio project?
– You have upgraded SQL Server 2012 to SQL Server 2016. Officially it is supported, but you take a risk when doing that. My last upgrade from SQL 2012 to SQL 2016 was a side by side installation on the DEV/TEST server, upgraded the packages, checked that everything worked, then uninstall SQL 2012. After that the same procedure on the PROD Server. But for that you might temporarily need additional disk space and memory on your server, I must admit that. In my case the server was virtual and this was not a big problem. But this advice clearly is not very useful for you anymore. Maybe for a next time.
If I were you I would split the problems: (1) Upgrade SSIS Packages (2) Fix Server Installation.
For (1) I would take your original SSIS 2012 packages, see if you can convert them to Biml on http://www.bimlonline.com, then create SSIS 2016 packages from the biml files.
For (2) I would try to uninstall SSIS 11.0 from the server. If this doesn’t work, at least set the Service to “Manual” or “Disabled” and make sure it is not running to save system resources and to avoid that you use the wrong SSIS Service by accident.
I do not know the Attunity Oracle driver and can not give advice on that.
Hope this answer brings you a bit further in solving the issues you are currently facing.
Best regards, Hans
Daniel,
Re. Attunity 4.0 driver, if the programs/features shows the Attunity driver as installed but there are no Oracle source/targets available in SSIS, then the install was not correct.
I recently blogged about this on linkedin…the attunity driver doesn’t stop an install if it isn’t correct. It only displays a warning message that it cannot find SqlServer in a message box popup. Attunity 4.0 requires local installation of SqlServer 2016 database software where you are installing. If you are installing the driver to something other than 2016 installation or are installing to client-only (such as PC or terminal server) then it won’t install correctly. Just install the 2016 developer edition database software (can disable the services switching to manual during install) and re-install Attunity and it should be fine. All my packages using attunity 2.0 worked fine with the new 4.0 driver. Also make sure you do this after installing Oracle client (11 or 12) and before upgrading the project.
Foreach Loop Container:Error: The GetEnumerator method of the ForEach Enumerator has failed with error 0x80004005 “Unspecified error”. This occurs when the ForEach Enumerator cannot enumerate.
From your comments it is very difficult – not to say impossible – to troubleshoot. Only debugging and/or comparing old and new SSIS package might reveal the reason for the error you are getting. Sorry I cannot help you right now.
FYI – After upgrading at the project level you may have to open each package and save it or you will end up with a corrupted project! At least that’s what it did to me.
After upgrading, open up one of your packages with a script task and if you see the following type of error message: Information while loading ….dtsx: The Script Task “….” has been migrated. The package must be save to retain migration changes. Then watch out – you need to do this for each package and make sure you save it.
If you don’t open each package and do this and instead save the entire project, then the next time you open up the project the individual packages may be corrupted.
Thanks for sharing this Gary, my overall conclusion is that upgrading SSIS packages is still buggy, and it will hopefully be improved in the future.
Hans, btw…you saved me a LOT of heartache by this blog post as I read it prior to upgrading so didn’t attempt the methods that don’t work. I owe you.
Also, you might note that I have found major issues with the more recent releases of SSDT (17.1/17.2) and do not recommend attempting to use them. See my article in linkedin for more details. I’m just completing a major migration from 2012 to 2016 that includes a lot of 3rd party SSIS plugins, anyone reading this is welcome to ping me on issues/gotchas.
Hi Gary, I did not have a look at SSDT 17 yet, just a bit too busy, and it is not used in my project yet.
If you got new insights on upgrading please keep me posted. Moving to developing your packages in Biml might be more futureproof for the next upgrade. But that is not of use for a bunch of packages already there to be upgraded, although you could try to convert them to biml first on bimlonline.com, then generate SSIS packages for the new version.
Anyway, good luck with it.
Best regards,
Hans
This is a very nice blog post. I am upgrading SSIS packages from VS 2008 to VS 2016 and packages that have the script task fail on the scrip task even if they have the scrip as is. can I get help on this?
Hello Bille, how many packages do you have?
Due to the response on this blog post I learned that upgrading script tasks often gives problems. Some things you could try:
(1) convert the SSIS 2008 packages to biml-code first on http://www.bimlonline.com using the “SSIS Package Importer”, then rebuild packages for SSIS 2016 from the biml files.
(2) first convert to SSIS 2012, and then to 2016.
(3) If you have reasonable number of packages (say not hundreds) you might make yourself angry and upgrade the packages first, then copy over each script task manually and compile it again. Depending on the number of packages of course this might be doable in a reasonable time.
thanks, the article very helpful.
Good to hear, Ben.
Hi Hans,
In my SSIS Packages Data flow tasks are corrupted.
that means I have created DFT from source to destination with some transformations and saved.
After that some time i opened pkg in DFT no components only empty DFT showing .
I’m not understanding whats going on.
Hello Ranjith, sorry to hear that. From a distance it is impossible to troubleshout, do you have a backup of the older packages to revert to? Maybe you can use the SSIS Package Importer on bimlonline.com and then use the biml files to upgrade to a newer SSIS version?
A very informative article on upgrading SSIS project to high version, Thank you Hans
Hi , I am updating the SSIS packages from 2012 to 2016 and your article helped me a lot. Now the issue am facing is when i executed this upgraded package using execute utility package it gives me error : To run a SSIS package outside SQL server data out tools you must install standard version of integration services or higher.
Hi Shruti,\Projects\\. Right click on the package name and choose “Execute” from the menu.
With the information you have given it is impossible to do remote trouble shooting as I did not ever encounter this error myself.
Without any knowledge on your server just a few comments:
– Check if you have installed standard version of integration services (as the message suggests you haven’t).
– if you have multiple versions of SQL Server installed on the same machine, make sure you invoke the right version of the Execute Package Utility
– test if the package runs in debug mode from within Visual Studio
– test if the package runs when executed from SSMS\Integration Services Catalogs\SSIS\
With the last two tests you can verify if the Execute Package Utility causes the issue or if the package doesn’t work at all.
Hope I have you some input for further investigation.
Best regards,
Hans
i am working on SSIS migration from 2012 to 2017.
installed VS 2017 and i updated target server version 2017.
after this i upgraded all ssis packages , some packages upgraded successfully, some packages throwing an warning .
Warning 0x40016044: DFT_LogErrorRecords: Found SQL Server Integration Services 2012 Script Component SCR_Log_ErrorRecords that requires migration!
how to resolves this isssue
Hello Sat, Script Components are the most difficult to upgrade, I once ended up in opening each converted package, opened the Script Component, pasted in the code from the old (v2012) package, then build and closed the Script Component. Maybe there are other automated ways, but as I do not upgrade packages daily, at that time it was not worth the effort finding that out, it was faster to just copy over the scripts.
My SSIS script tasks were getting totally gutted when upgrading from 2012 to 2016, even when I tried the “upgrade all packages” option as you suggested in your updated post. Ultimately, the only thing that seemed to work was upgrading the Target Server Version from 2012 to 2014 first, saving it, and then upgrading the Target Server Version from 2014 to 2016. Hope this helps other people that may be having the same issue.
Hi Squeedle,
Thanks for your suggestion.
What I noticed myself is, that when you first upgrade an empty SSIS project from 2012 to 2016, then paste the 2012-SSIS Packages into it and open them one by one, they will get upgraded correctly, including the script tasks.
Best regards,
Hans
Do you think the above statement work for upgrade 2012 to 2017? I have issue with script tasks as well .
Thanks
Hi Edi,
To be honest I do not know. My advice is to try with one package. If it works you are fine. Otherwise you might consider upgrading to SSIS 2016 first, and then from 2016 to 2017. Good luck!
Kind regards,
Hans