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

If you get this error when you are trying to expand the MSDB section in SSIS and your target SQL Server is clustered then this is probably because the default reference to an instance of SQL Server in the SSIS configuration file is pointing at “.” (which is the alias for local server). As the locally installed instance of sql server is only a node of a cluster and not a standalone sql server then “.” is not a valid server name.

To get a “.” server name you would have to be on the cluster, not the node machine

Simply edit the config file for SSIS and enter the name of the cluster instead.

e.g.

The file is at C:\Program Files\Microsoft SQL Server\100\DTS\Binn\MsDtsSrvr.ini.xml

and contains (by default)

<?xml version="1.0" encoding="utf-8"?>
<DtsServiceConfiguration xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<StopExecutingPackagesOnShutdown>true</StopExecutingPackagesOnShutdown>
<TopLevelFolders>
<Folder xsi:type="SqlServerFolder">
<Name>MSDB</Name>
<ServerName>.</ServerName>
</Folder>
<Folder xsi:type="FileSystemFolder">
<Name>File System</Name>
<StorePath>..\Packages</StorePath>
</Folder>
</TopLevelFolders>
</DtsServiceConfiguration>

Change the <ServerName>.</ServerName> bit, replacing . with the cluster name.

You will now have to restart the SSIS service to force it to read the new configuration file. NOTE THAT YOU DO NOT HAVE TO RESTART THE BOX OR SQL SERVER! JUST SSIS.

Note: To edit the config file you may need to use an editor in administrator mode depending on the version of windows. Open notepad (or whatever) using right click and Run as Administrator then browse for the file.
I recommend turning off the hide extensions for known types. If you want to do this then use Windows explorer, press left ALT and F then on Tools menu choose Folder options to search for this option.

Sadly the rest of the information presented by the error message is a tad misleading but it is factually correct as the SQL Server really is not found (there is no local sql server in a cluster node)…


ADDITIONAL INFORMATION:

This might occur when there is no default instance of SQL Server on the computer.

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. (MsDtsSrvr)

Regards

Dave

About these ads

About davidbridge

I run David Bridge Technology Limited and specialise in Database design and development including administration and web application development using .net
This entry was posted in Developer stuff, SQL Stuff and tagged , , , , . Bookmark the permalink.

3 Responses to The SQL Server instance specified in SSIS service configuration is not present or is not available

  1. Humberto says:

    Excelent! resolv my problem

  2. Mahsa says:

    Finally. A solution that actually worked. Thank you so much for the post.

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