Share via

SSIS Troubleshooting: The SQL Server instance specified in SSIS service configuration is not present or is not available.

  • Article

Error message

Failed to retrieve data for this request. (Microsoft.SqlServer.Management.Sdk.Sfc)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%20SQL%20Server&LinkId=20476

Additional information:

The SQL Server instance 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 SQL Server 2012 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.

SQL Server Network Interfaces: Error Locating Server/Instance Specified [xFFFFFFFF].  (MsDtsSrvr)

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.

SQL Server Network Interfaces: Error Locating Server/Instance Specified [xFFFFFFFF].  (Microsoft SQL Server Native Client 11.0)

SOLUTION

Location of SQL Server Integration Services Configuration File

SQL Server Integration Service (SSIS Service) relies on “MsDtsSrvr.ini.xml” configuration file for its settings. By default, the configuration file is located in %ProgramFiles%\Microsoft SQL Server\xxx\DTS\Binn folder. Where “xxx” refers to the SQL Server version.

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

SQL 2012 location:

C:\Program Files\Microsoft SQL Server\110\DTS\Binn\MsDtsSrvr.ini.xml

  • STOP SSIS Services
  • Replace the Server Name node with Clustered Instance Name (replace “.” or ".\OLDSERVERNAME"  With cluster instance name i.e., SERVERNAME\INSTANCENAME)

http://3.bp.blogspot.com/-WgfGX4oSKuw/VOSz4oZ8fWI/AAAAAAAAEf8/_2ZyAhXdqLc/s1600/1.png

Restart the SQL Server Integration Service (SSIS Service).

You can restart the Integration Services service by using SQL Server Configuration Manager or the following PowerShell command:

restart-service -name MsDtsServer100

You can manage ssis packahe in Multiple instance.
https://mikesdatawork.files.wordpress.com/2016/09/image002.png?w=829