SQL Server SSIS: Package migration from version 3 to version 2 failed with error 0xC001700A
I got a error during SSIS package execution:
Executed as user: NGM\sqlprod. …ersion 9.00.3042.00 for 64-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved. Started: 3:13:51 PM Error: 2013-12-09 15:13:51.85 Code: 0xC001700A Source: Description: The version number in the package is not valid. The version number cannot be greater than current version number. End Error Error: 2013-12-09 15:13:51.85 Code: 0xC0016020 Source: Description: Package migration from version 3 to version 2 failed with error 0xC001700A “The version number in the package is not valid. The version number cannot be greater than current version number.”. End Error Error: 2013-12-09 15:13:51.85 Code: 0xC0010018 Source: Description: Error loading value “<DTS:Property xmlns:DTS=”www.microsoft.com/SqlServer/Dts” DTS:Name=”PackageFormatVersion”>3</DTS:Property>” from node “DTS:Property”. End Error Could not load package “N:\DBA\WC_Exp_Mod_NC_CDX\WC_Exp_Mod_NC_CDX_1.dtsx”… The package could not be lo… The step failed.
The failed agent jobs were those, which had steps to execute SSIS packages and they were failing with the error message “Description: The version number in the package is not valid. The version number cannot be greater than current version number.”
On investigating the root cause, what we found out was the below:
1. The SSIS subsystem during the SQL Agent job run was trying to execute DTexec.exe from the folder “c:\Program Files\Microsoft SQL Server\90\DTS\BINN”
2. What happens on boxes running SQL version 2005 and Integration service 2008 is that, whenever a SQL 2005 patch is applied, the value of default key at the location “HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSDTS\SetUp\DTSPath” in the registry gets reset to “c:\Program Files\Microsoft SQL Server\90\DTS\”
1. Manually modify the value to reflect the correct value “c:\Program Files\Microsoft SQL Server\100\DTS\”.
2. If your SQL instance is on a cluster, you need to update the registry on all the cluster nodes.
3. Restart SQL integration service and SQL Agent Service. No need to restart the node or SQL Instance service.
4. SQL instance picks up the newly modified value and the same can be verified by querying the table msdb..syssubsystems. Look for the SSIS subsystem record and the value in the field “agent_exec”
Note: This means, unless SQL instance is also upgraded to version 2008, whenever I apply a CU patch to the SQL 2005 instance, I also need to remember to manually fix the registry key on all the nodes.