The Business Intelligence Blog

December 26, 2009

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 by ServerName\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.

Advertisements

2 Comments »

  1. books online are great, wether they are e-books or conventional hardbound and paperback books .`~

    Comment by Body Scrub Recipes — November 24, 2010 @ 7:29 pm

  2. Ths is useful. I have a question though. What about for multiple SQL instances in a cluster? How should this be configured?

    Comment by LaFilipina — January 25, 2011 @ 9:10 am


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Blog at WordPress.com.

%d bloggers like this: