Notice: This website is an unofficial Microsoft Knowledge Base (hereinafter KB) archive and is intended to provide a reliable access to deleted content from Microsoft KB. All KB articles are owned by Microsoft Corporation. Read full disclaimer for more details.

You are not able to connect to SQL Server 2005 Integration services using Management Studio from SQL Server 2008 or later versions


Symptoms

When you try to connect to SQL Server 2005 Integration Services (SSIS 2005) either using SQL Server Management Studio (SSMS) from SQL Server 2008 or SQL Server 2008 R2 you will experience one of the following error messages:

SQL Server 2008 RTM or Service Pack 1(SP1) versions:

Hresult: 0xC00160AA
Integer:  -1073651542
Symbolic constant: DTS_E_CONNECTTOSERVERFAILED
Error Text: Connect to SSIS Service on machine "servername" failed:Class not registered.".

SQL Server 2008 SP2 or later versions and SQL Server 2008 R2 or later versions:

Hresult: 0xC00160AC 
Integer: -1073651540 
Symbolic constant: DTS_E_CONNECTTOSERVERFAILED_NOTREGISTERED 
Error Text: Connecting to the Integration Services service on the computer "__" failed with the following error: "__".
This error can occur when you try to connect to a SQL Server 2005 Integration Services service from the current version of the SQL Server tools. Instead, add folders to the service configuration file to let the local Integration Services service manage packages on the SQL Server 2005 instance..

↑ Back to the top


Cause

This behavior is by design. Microsoft does not support connecting to SSIS 2005 using SSMS  from later versions like SQL Server 2008 or SQL Server 2008 R2.  Similarly you cannot connect to the newer versions of SSIS (2008 or 2008 R2) using SSMS from SQL Server 2005.

↑ Back to the top


Workaround

You can use one of the following workarounds:

Workaround 1: Install SSMS 2005 side-by-side with either SSMS 2008 or SSMS 2008 R2 on systems where SSIS 2005 access is required and use the respective versions of SSMS to manage the SSIS instances.

Note: Two or more versions of SQL Server Management Studios can be installed side-by-side on the same client machine. The Integration Services installation itself is not instance aware and both SSIS 2005 and SSIS 2008 (or SSIS 2008 R2) can be installed side-by-side on the same system and listen on same machine name. But each service will only allow connections from a matching version of the client tools.

Workaround 2: Configure your SSIS 2008 (or SSIS 2008 R2) instance to manage your SSIS 2005 packages. To do this you update the MSDtsSrvr.ini.xml file for the 2008 (or 2008 R2 ) SSIS instance so that it includes the information about the  MSDB database that is used to store your SSIS 2005 packages.

Note The MsDtsSrvr.ini.xml file is located in the following folder:

%ProgramFiles%\Microsoft SQL Server\100\DTS\Binn

Example excerpt from MsDtsSrvr.ini.xml:
   
<Folder xsi:type="SqlServerFolder">
      <Name>MSDB from SQL 2005</Name>
      <ServerName>YourSQLServer2005\InstanceName</ServerName>
    </Folder>

However, consider the following two caveats when using this second workaround:

Runtime: When you execute an SSIS 2005 package using the object explorer in  SSMS2008 (by right clicking on the package and selecting Run), the object explorer will launch the 2008 (or later) version of   DTExecUI.exe runtime on the client machine to execute the package. Since that involves executing a 2005 package using a 2008 version of the tool, the tool (DTEXecui.exe) will automatically upgrade the package format to the newer 2008 package format before is it executed. However, since the newer format is not persisted just by running the package, your package will still exist as a SSIS 2005 package that can still be accessed by SSMS from a SQL Server 2005 instance.

Import & Export: Making package changes by Importing or Exporting packages via this object explorer interface across versions is not recommended. SSIS 2008 package format could be inadvertently saved into the SQL 2005 MSDB or file system, or SSIS 2005 packages can inadvertently be upgraded to SSIS 2008 format upon export to MSDB or file system. When this happens, the package formats could become mismatched and the upgraded packages can no longer be run using SSIS 2005 runtime. When you use Import or Export functionality from Management Studio in SQL Server 2008 or later versions,  it persists the package in the format that matches the version of Management Studio, instead of the original 2005 format. Though the runtimes from SSIS 2008 or SSIS 2008 R2 can execute SSIS 2005 packages, SSIS 2005 runtimes cannot execute SSIS 2008 (or later version) packages. 

You can look at the following XML tag inside a persisted .Dtsx package file to notice the difference in package formats:

SSIS 2005 Format:
<DTS:Property DTS:Name="PackageFormatVersion">2</DTS:Property>

SSIS 2008 Format:
<DTS:Property DTS:Name="PackageFormatVersion">3</DTS:Property>

↑ Back to the top


Keywords: kb

↑ Back to the top

Article Info
Article ID : 2466166
Revision : 1
Created on : 1/7/2017
Published on : 12/28/2010
Exists online : False
Views : 120