SSIS在打开MSDB 报错

以下的方法原路径：

https://davidbridge.wordpress.com/2013/08/08/the-sql-server-instance-specified-in-ssis-service-configuration-is-not-present-or-is-not-available/

报错如下：

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.

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

注：这个路径好像根据版本不一样而定，我的路径就不是这个，是这个：C:\Program Files\Microsoft SQL Server\140\DTS\Binn

，你可以在C:\Program Files\Microsoft SQL Server下面搜索，这个文件好像就只有一个

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.

上一句话的注释：我的server全名是：CSHW334.prod.ats.net\STG，修改后为：<ServerName>.\STG</ServerName>，修改的方法：如果你直接在里面修改是权限问题不可以修改的，你要把这个文件Copy出来进行修改，然后再copy回去进行覆盖就可以了。

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)