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.
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">
<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)…
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)