When performing an edition upgrade on SSIS Service Pack 2 + Cumulative Update its best to uninstall the CU *before* you run the edition upgrade, this will avoid some issues that can occur when following the upgrade you come to re-apply Service Pack 2 and Cumulative Update.
So what happens if you don’t ?
Taking the following example, Integration services is installed and patched to SP2 + a hotfix, take as an example the 3050 GDR build. We then attempt an edition upgrade of SSIS , by running the following command from the cmd prompt.
Start /wait setup.exe ADDLOCAL=SQL_DTS UPGRADE=SQL_DTS SKUUPGRADE=1 /qb
Following the upgrade if you look at the following registry key HKLM\Software\Microsoft\Microsoft SQL Server\90\dts\setup
The version indicated is 9.0.1399.06, but the key file for Integration Services MSDTSSRVR.EXE has a version property indicating 9.0.3050.00
This causes us issues when we try and apply SP2 because the key file is later than SP2, it would be invalid to attempt to apply SP2 since this would be going to an earlier version, you will see the following in the summary file
Time: 12/15/2008 16:27:39.382
KB Number: KB921896
Machine: AP1111245CC
OS Version: Microsoft Windows Server 2003 family, Enterprise Edition Service Pack 2 (Build 3790)
Package Language: 1033 (ENU)
Package Platform: x86
Package SP Level: 2
Package Version: 3042
Command-line parameters specified:
Cluster Installation: No
**********************************************************************************
Prerequisites Check & Status
SQLSupport: Passed
Products Detected Language Level Patch Level Platform Edition
Setup Support Files ENU 9.2.3042 x86
Database Services (MSSQLSERVER) ENU SP2 2005.090.3042.00 x86 STANDARD
Database Services (STD1) ENU SP2 2005.090.3299.00 x86 ENTERPRISE
Notification Services ENU SP2 9.00.3299.00 x86 STANDARD
Integration Services ENU RTM 9.00.3050.00 x86 ENTERPRISE
SQL Server Native Client ENU 9.00.3042.00 x86
MSXML 6.0 Parser ENU 6.10.1129.0 x86
SQLXML4 ENU 9.00.3042.00 x86
Backward Compatibility ENU 8.05.2004 x86
Microsoft SQL Server VSS Writer ENU 9.00.3042.00 x86
Products Disqualified & Reason
Product Reason
Database Services (MSSQLSERVER) This installation must be run from the active node. You are running it from a passive node. To proceed, cancel the installation and run it again from the active node.
Database Services (STD1) The product instance STD1 been patched with more recent updates.
Notification Services The product instance NS been patched with more recent updates.
Integration Services The product instance Integration Services been patched with more recent updates.
Processes Locking Files
Process Name Feature Type User Name PID
Summary
All product instances were disqualified
Exit Code Returned: 11300
So we can’t get SP2 installed so lets try a later hotfix....it fails too L, this time when we look at the summary file we see the following
Time: 12/15/2008 16:29:04.434
KB Number: KB960110
Package Version: 3299
SQL Server Database Services 2005 (MSSQLSERVER) ENU SP2 2005.090.3042.00 x86 STANDARD
SQL Server Database Services 2005 (STD1) ENU SP2 2005.090.3299.00 x86 ENTERPRISE
SQL Server Notification Services 2005 ENU SP2 9.00.3299.00 x86 STANDARD
SQL Server Integration Services 2005 ENU RTM 9.00.3050.00 x86 ENTERPRISE
SQL Server Database Services 2005 (MSSQLSERVER) This installation must be run from the active node. You are running it from a passive node. To proceed, cancel the installation and run it again from the active node.
MsDtsServer Integration Services Service 1392
Product Installation Status
Product : SQL Server Database Services 2005 (MSSQLSERVER)
Product Version (Previous): 3042
Product Version (Final) :
Status : NA
Log File :
Error Description : This installation must be run from the active node. You are running it from a passive node. To proceed, cancel the installation and run it again from the active node.
----------------------------------------------------------------------------------
Product : SQL Server Database Services 2005 (STD1)
Product Version (Previous): 3299
Status : Not Selected
Error Number : 0
Error Description :
Product : SQL Server Notification Services 2005
Product : SQL Server Integration Services 2005
Product Version (Previous): 3050
Status : Failure
Log File : C:\Program Files\Microsoft SQL Server\90\Setup Bootstrap\LOG\Hotfix\DTS9_Hotfix_KB960110_sqlrun_dts.msp.log
Error Number : 1642
Error Description : Unable to install Windows Installer MSP file
One or more products failed to install, see above for details
Exit Code Returned: 1642
So the hotfix won’t apply because SP2 is a pre-requisite and based on the registry information (9.00.1399.06) we are at RTM.
So no way forward and no way back.
The method to workaround this issue is to
1) On a dummy server install Integration Services (Enterprise edition)
2) Stop SSIS services on both the ‘broken’ server and the dummy server.
3) Copy an RTM version of MSDTSRVR.EXE into the program files directory to "C:\Program Files\Microsoft SQL Server\90\DTS\Binn\" on the ‘broken’ server.
4) Apply SP2
5) Apply hotfix.
As a side note the above would work equally well with an SP2 (build 3042) version of MSDTSRVR.EXE.