Wednesday, September 16, 2009

"SQL Server specified in SSIS Service configuration is not present"

PROBLEM: After connecting to SSIS (SQL Server Integration Services) from SSMS (SQL
Server Management Studio), when you expand MSDB under Stored Packages to see list
of stored packages, you may get below error.




ERROR MESSAGE:

TITLE: Microsoft SQL Server Management Studio
------------------------------
Failed to retrieve data for this request. (Microsoft.SqlServer.SmoEnum)
For help, click:
http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&LinkId=20476
------------------------------
ADDITIONAL INFORMATION:
The SQL server specified in SSIS 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 Server 2005 Books Online.

Login timeout expired
An error has occurred while establishing a connection to the server. When
connecting to SQL Server 2005, this failure may be caused by the fact that under
the default settings SQL Server does not allow remote connections.
SQL Network Interfaces: Error Locating Server/Instance Specified [xFFFFFFFF].
(MsDtsSrvr)
------------------------------
Login timeout expired
An error has occurred while establishing a connection to the server. When
connecting to SQL Server 2005, this failure may be caused by the fact that under
the default settings SQL Server does not allow remote connections.
SQL Network Interfaces: Error Locating Server/Instance Specified [xFFFFFFFF].
(Microsoft SQL Native Client)
------------------------------
BUTTONS:
OK
------------------------------

Resolution:
====================
CAUSE: This problem may happen because below multiple reasons.

1. Registry key "HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSDTS\ServiceConfigFile" not
existing or not pointing to correct path of the file MsDtsSrvr.ini.xml. Default
path of this file is "%ProgramFiles%\Microsoft SQL Server\90\DTS\Binn\MsDtsSrvr.ini.xml".

2. The content of file MsDtsSrvr.ini.xml typically looks like below.


xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
true


MSDB
.


File System
..\Packages




If the Packages folder path specified between StorePath tags above is incorrect,
you may get the same error.

3. If the ServerName specified between ServerName tags above is incorrect, you may
get the same error. If the SQL is in cluster, specify the virtual name.

RESOLUTION: In our case, we replaced '.' with the netbios name of the server and it
resolved the issue.

1 comment:

Khalid Ashraf said...

I changed the . to machinename\instancename and restarted the SSIS and it worked.