A couple of months back our infrastructure team had undertaken a project to upgrade server hardware and the OS as part of their objective to make systems more resilient.
For the SQL boxes, the mandate was to carry out a side by side upgrade. OS was to be upgraded to windows 2008. SQL instance version to remain the same at 2005, but we decided to take this opportunity to upgrade SQL Integration services to version 2008.
After the infrastructure team handed over the new boxes to the DBA team after the OS build and configuration, DBA team mates(s) assigned on the up-gradation task went about with the install of SQL instance(s) on the new box.
After the SQL instance(s) was installed and configured as per our organizational standards, SQL Integration service 2008 was also installed.
Then the newly installed SQL 2005 instance(s) were patched to the current production version.
Finally during the advertised downtime window, DBA(s) went about the process of migrating the SQL instance(s) from the old box to the new(ly) configured one. After the requisite pre-planned migration steps were carried out, SQL services were restarted and all initial level test case checks turns out to be a success.
Advertised downtime window about to get over, when the DBA(s) start the process of re-running SQL Agent jobs that had missed their configured schedule [during the time the migration was happening] and hard luck, some of the agent job(s) [not all] start failing.
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:
- 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”
- 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\”
- Manually modify the value to reflect the correct value “c:\Program Files\Microsoft SQL Server\100\DTS\”.
- If your SQL instance is on a cluster, you need to update the registry on all the cluster nodes.
- Restart SQL integration service and SQL Agent Service. No need to restart the node or SQL Instance service.
- 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.