Friday, September 30, 2011

Configuring Integration Services named instance

When you have named instance of SQL Server Integration Services, you will get the following error if you try to access the packages that are stored in the MSDB database.

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
A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online.Named Pipes Provider: Could not open a connection to SQL Server [53]. (Microsoft SQL Server Native Client 10.0).
To get around this error, you need to configure the MsDtsSrvr.ini.xml file which is available under the path C:\Program Files\Microsoft SQL Server\100\DTS\Binn. Open the MsDtsSrvr.ini.xml file in notepad and edit it as shown in the figure below.

Please note that in the original file, the server name which is denoted by a '.' has been replaced byServerName\InstanceName, where ServerName is the name of the server on which Integration Services has been installed and InstanceName is the named instance of Integration Services. After saving the xml file, be sure to start the Integration Services service for changes to take effect.

No comments:

Post a Comment