Tablets The SQL Server instance specified in SSIS service configuration is not present or is not available. | BLOG IT

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

I have an Active/Passive SQL Cluster configuration with two SSIS instances one located at each cluster's node.

I tried to display the SSIS packages located on the SSIS service but I receive the following error:
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 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 [2]. (MsDtsSrvr)

To configure the SSIS Service is necessary to open the following file:
\Program Files\Microsoft SQL Server\100\DTS\Binn\MsDtsSrvr.ini.XML

I tried several configurations but none works for me, I performed a lot of searchs on Internet but I could not find a solution.

To solve the issue I open the SQL Error Log file and locate the following entry:
Server local connection provider is ready to accept connection on
[ \\.\pipe\SQLLocal\MSSQLSERVER ].


After that I open the SSIS Service configuration file on both cluster nodes and configure the following line in the ServerName Node:
\\.\pipe\SQLLocal\MSSQLSERVER

And finally I restarted the SSSIS service and open a connection to the SSIS service and expand the MSDB database and finally I could see all the SSIS packages located on it.

This solution is incredible but is real, I hope it helps more people. I don't know why the configuration file does not work with other parameters like Server Name or TCP/IP Address.
SHARE

About Robert SA

    Blogger Comment
    Facebook Comment

4 comentarios:

Sandip Patel said...

Wow- Great work. I spend couple of hour but nothing work. This is what solve my issue. Thanks for great post.Keep it up.

Robert SA said...

Thanks for your comments, it's nice to hear that this procedure help you.

Anonymous said...

This saved me a lot of time , fixed the problem straight away!

Thank you!

Anonymous said...

This was supremely helpful. I too saw other posts about renaming the ServerName element to something like .\SQLSERVERInstanceName

But this didn't work. I didn't have a default node in my cluster.

Using named pipes like \\.\pipe\SQLLocal\InstanceName did the trick.