Could not load package because of error 0x80070002 while upgrading from SSIS 2008 to SSIS 2012

When doing an In-place upgrade from Microsoft SQL Server 2008 or 2008 R2 to SQL Server 2012, be aware of this little caveat with SSIS.

Last week I upgraded (note, I did an in-place upgrade and not a side-by-side installation) my Sql 2008 instance to Sql 2012. Everything went smooth before I was able to discover that all my Sql jobs running SSIS packages are now failing with errors complaining about incompatible package versions. Depending on the version on which the original package was built, the error messages could be:

Could not load package <package_name> because of error 0x80070002.
Description: Unable to find the specified file.
Source: MsDtsSrvr
OR
Description: The version number in the package is not valid. The version number cannot be greater than current version number.

I was even more surprised when I see that the run time execution is still trying to trigger the 2008 version of Dtexec.exe:
Microsoft (R) SQL Server Execute Package Utility
Version 10.50.2500.0 for 64-bit

The obvious question is why am I still seeing the references of Sql 2008? Doesn’t in-place upgrade mean that the new version would exist and it would completely wipe out the previous versions? Well apparently, it does not seem so and here is why:

When you upgrade to SQL Server 2012 Integration Services (SSIS) on a machine that has SSIS 2005 or 2008 installed, SSIS 2012 is installed side-by-side with the earlier version to sustain some backward compatibility features.. Unlike upgrades to previous versions, the upgrade process does NOT remove the SSIS 2005 or 2008 files, service, and tools.

Integration Services is a shared feature in SQL Server setup, meaning that each version can only be installed once, and it is not instance specific since it is shared across any number instances of SQL Engine, Reporting Services, and Analysis instances installed on the box.

SQL 2012 setup does not remove the original shared components for other components either, including Integration Services, so it leaves the original version in-tact, and adds the new version side-by-side. It just so happens the Integration Services feature is the only shared component with a server-side service, so it’s a little bit heavier of impact than the other Shared Features which are mostly client side things like Management Studio and SQL Server Data Tools (BIDS) which you may not notice as readily.

In addition, with this side-by-side install, multiple versions of Dtexec utility are installed. To ensure that you run the correct version of the utility, at the command prompt run the utility by entering the full path ( <drive> :\Program Files\Microsoft SQL Server\<version>\DTS\Binn).

After the in-place upgrade is complete, you will see 2 SSIS services in SQL Server Configuration Manager:- one for SQL Server 2008 and one for SQL Server 2012:

For more information about upgrading to 2012 SSIS, see Upgrade Integration Services and 5 Tips for a Smooth SSIS Upgrade to SQL Server 2012.

Author : Debarchan(MSFT) SQL Developer Engineer, Microsoft

Reviewed by : Jason(MSFT), SQL Developer Escalation Services, Microsoft