Configure SSIS to SQL Server 2012 Cluster

SQL Server Integration services are not cluster aware by default, the following article shows a documented steps by Microsoft on how to configure SSIS to Cluster.

Install SSIS on all nodes of a cluster, If SSIS is installed after the SQL DB Cluster is built you need to select Stand-alone installation route(As shown below) as you cannot add SSIS to an existing SQL cluster during installation.

Open Failover Cluster Manager, connect to the cluster role to which SSIS need to be added as resource(usually SQL Server Role), Right Add Resource->Generic Service

 

Select SQL Server Integration Services and Click Next

Click Next

Click Finish, Now you should see SSIS added to as a resource of the Cluster Role.

Now Right Click on SQL Server Integration Services 11.0 Resource and select properties, In the Properties box go to Dependencies Tab and add SQL Server Resource and a disk resource where SSIS config file will be placed (We will look about config file in the next step)

 

Go to C:\Program Files\Microsoft SQL Server\110\DTS\Binn\ copy MsDtsSrvr.ini.xml to the drive(Drive must be a part of SQL Server cluster role) and folder you have selected, I have chosen T:\SSIS.

 

Open this file in notepad modify <ServerName>.</ServerName> to <ServerName>SQLServer1\TEST2012</ServerName> with the SQL Server instance name in the cluster and <StorePath>..\Packages</StorePath> to some UNC path on the clustered drive if you store packages to file system and save the file. Sample file below

 

<?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>SQLServer1\TEST2012</ServerName>

    </Folder>

    <Folder xsi:type="FileSystemFolder">

      <Name>File System</Name>

      <StorePath>T:\SSIS\Packages</StorePath>

    </Folder>

  </TopLevelFolders>

</DtsServiceConfiguration>

 

Go to Registry and Update the value of HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\110\SSIS\ServiceConfigFile to the UNC path and file name of the service configuration file on the select clustered disk. In my case T:\SSIS\MsDtsSrvr.ini.xml

 

 

Once the registry is updated SSIS has been successfully updated to cluster. Try starting SSIS resource in the Cluster and try failing over to other nodes on the cluster.