Rahman Agoro, the frontline SQL DBA

Just another WordPress.com weblog

Archive for October, 2011

Configuring and Installing SSIS on a SQL 2008 R2 failover cluster infrastructure.

Posted by rahmanagoro on October 23, 2011

SSIS isn’t cluster aware out of the box, so there is some bit of config changes to make in order to make it work on a cluster.


Select just the Integration service.

On the Client Access point screen, type in the name and also the IP address as well looking at the requirement spreadsheet.

Click on Next

Since the SSIS service has been installed in its own resource group, we now need to set the dependencies.

Set the disk dependency for the SSIS service

Add the disk resource dependency

Requirement: Change SSIS configuration, custom configuration

SSIS Custom Configuration

Navigate to the path: C:\Program Files (x86)\Microsoft SQL Server\100\DTS\Binn

Create a folder on the root of the SSIS cluster disk called SSIS_Config

Create a folder on the SSIS cluster disk called Packages

Copy the file from the location C:\Program Files\Microsoft SQL Server\100\DTS\Binn\MsDtsSrvr.ini.xml to S:\SSIS_Config\

Right Click the SSIS service and select properties and then go onto Registry replication and add the registry key below.

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\100\SSIS\ServiceConfigFile\

Click on OK and then Apply.

Take the resource offline and bring it back online for the changes to take effect.

Perform the following steps for SSIS on the second node.

Change the MsDTSSrvr.ini.xml file on the location defined above and change the packages folder to point to S:\Packages

Make the registry entry changes on the second node as well to finish off the configuration.

Test the failover by failing over the SSIS resource group by NodeA to NodeB.

Copy a .dtsx file onto the S:\Packages folder and open up management studio.

In the illustration below, you can see that we have connected to the SSIS instance and we can see the packages.



Posted in Clustering, SQL 2008, Tips, Uncategorized | 5 Comments »