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.