Watch Out For SSIS Package Migration/Upgrade Errors

After successfully upgrading a multi-instance SQL Server 2005 Failover Cluster to SQL Server 2008 R2, I got woken up today by a page from a SQL Server Agent job failure. Knowing that the job was created by one of my colleagues, I ignored it for a while since it only runs once a week and is not really that critical since it only performs tasks for reporting purposes. However, I just can't seem to get it off my head that what seems to be a successful upgrade caused some issues on the SQL Server Integration Services (SSIS) components. I checked the SQL Server Agent Job History for more information but only found this bit of information in the error log

The package failed to load due to error 0xC0010014

After a quick search on Google, I found this blog post on the MSDN Blogs site that explains what could be a possible cause for the error. While the blog post defined a different error number from the one I had, I was sure that this was it. Knowing that I had SQL Server 2005 Failover Cluster before, I had a feeling that the DTExec.exe executable file was still hanging around. True enough, I saw the EXE file. And the SQL Server Agent job that failed points to the old version of the DTExec.exe file. I immediately changed the path from within the SQL Server Agent job to point to the new version of the DTExec.exe file. The command which points to the old path looks like this

C:\Program Files (x86)\Microsoft SQL Server\90\DTS\Binn\DTExec.exe

After modifying the path, I now have this

C:\Program Files\Microsoft SQL Server\100\DTS\Binn\DTExec.exe

One thing to look for after upgrading a SQL Server 2005 instance to SQL Server 2008/R2 (whether it is a failover cluster instance or not) is to make sure that your SSIS packages are reconfigured to point to the new path of the DTExec.exe executable and test them accordingly