The Problem: Can’t Connect to Named Instance of SSIS in Management Studio

I’ve occasionally run into the following error when attempting to connect to SSIS from Management Studio:
"Failed to retrieve data for this request. (Microsoft.SqlServer.SmoEnum)
The SQL Server specified in Integration Services service configuration is not present or is not available. This might occur when there is no default instance of SQL Server on the computer. For more information, see the topic "Configuring the Integration Services Service" in SQL Server 2008 Books Online.
Login Timeout Expired
An error has occurred while establishing a connection to the server. When connecting to SQL Server 2008, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections.
Named Pipes Provider: Could not open a connection to SQL Server [2]. (MsDtsSvr)."
The problem, as it turns out, is that when I installed SQL Server 2005/2008 on a machine, that a default instance was already installed. In my case SQL Server 2000 was already installed as the default instance. The initial posts I read (and heard about in talking to other colleagues who had the same problem) said that SQL Server 2005/2008 had to be installed as the default instance  if you wanted to be able to use SSIS.
The problem is that unlike the database engine, which can have many running named instances running, SSIS can only run one instance on a server at a time. When SQL Server 2005/2008 is installed there is no option to specify which SQL Server instance should be used to run SSIS. Instead the default configuration is always applied and SSIS tries to start under the default instance. If the default instance is SQL Server 2000, you have a problem. You can only run SSIS on SQL Server 2005 or higher.

The Solution: Modify the Configuration File

Since a server can only run one instance of SSIS you need to make sure that the config file points to the correct instance of SQL Server. Locate the MsDtsSrvr.ini.xml configuration fileand open it with a text or xml editor.
SQL 2005 location:
C:\Program Files\Microsoft SQL Server\90\DTS\Binn\MsDtsSrvr.ini.xml
SQL 2008 location:
C:\Program Files\Microsoft SQL Server\100\DTS\Binn\MsDtsSrvr.ini.xml
The default contents of the file:

Notice the ServerName element points to the default instance of SQL Server (when connecting to the local machine “.” is the same as “(local)” – they both use NamedPipes to connect).
Modify the configuration file to point to the named instance as follows.

After making the change you must restart the SSIS service for the change to take effect.

Categories: Misc

Tags: SSIS