Issues when running SQL Server Integration Services in a Clustered Environment


Feb 18, 2014

Introduction

SQL Server Integration Services service always relies on configuration file for its settings. This article explains the steps you need to follow to configure the Integration Services Service (SSIS Service) in a clustered environment.

In a SQL Server clustered environment you may end up seeing below mentioned error message. Follow the below mentioned steps to resolve this issue.

Error Message

Failed to retrieve data for this request. (Microsoft.SqlServer.SmoEnum)

The SQL Server specified in Integration Services 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 2008 Books Online.

Login Timeout Expired

An error has occurred while establishing a connection to the server. When connecting to SQL Server 2008, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections.

Named Pipes Provider: Could not open a connection to SQL Server [2]. (MsDtsSvr).

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” referes to the SQL Server Version.

Resolution

The above error is encountered because SSIS is not a cluster aware service and Microsoft doesn’t recommend adding it to Failover Cluster. As a result when you are installing SSIS it will be installed on the first node as like any other non cluster service. In order to fix this issue you can follow the below mentioned steps.

Newsletter Signup

Steps to Resolve this Issue

  • Stop the SQL Server Integration Service (SSIS Service).
  • Open MsDtsSrvr.ini.xml file which is available in "C:\Program Files\Microsoft SQL Server\100\DTS\Binn\” folder in a notepad.

Sample MsDtsSrvr.ini.xml File

<?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>


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

Modified MsDtsSrvr.ini.xml File

<?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\INSTANCENAME </ServerName>
</Folder>
<Folder xsi:type="FileSystemFolder">
<Name>File System</Name>
<StorePath>..\Packages</StorePath>
</Folder>
</TopLevelFolders>
</DtsServiceConfiguration>


  • Restart the SQL Server Integration Service (SSIS Service).

Once you have followed the above mentioned steps the issue will be resolved.

Share this Article



Recommended Articles…



Follow @MyTechMantra on Twitter
We're on Facebook

"Receive newsletters and special offers about SQL Server, BizTalk and SharePoint from MyTechMantra. We respect your privacy and you can unsubscribe at any time."