How to add Integration Services to an existing SQL Server Clustered Instance

Step 1

To add/install SSIS on an installed SQL Server Instance, we need to run setup again on each cluster node. First run SQL Server setup on your active node (Node 1). Follow all necessary steps in the setup windows. Make sure to run this setup to add SSIS on your existing instance rather than creating a new instance. Choose Integration Service on the feature selection page of the setup window. After we start the install, we will get an error because one of the rules will fail as shown below.

Step 2

When you get the above error, you cannot proceed with the install because the "Next" button will be disabled due to this failed rule.

Step 3

As we already saw that we cannot add SSIS to an existing SQL Server cluster, the solution is to run setup and skip the installation rules to install SQL Server Integration Services in an existing clustered instance.

Run the below command at the Windows command prompt to start SQL Server setup on the active node.  Make sure to run this command after changing the root directory of the command prompt to the location where you have placed the SQL Server setup files.

Setup.exe /SkipRules=StandaloneInstall_HasClusteredOrPreparedInstanceCheck /Action=Install

Once you press enter to run the command, the SQL Server product version will display on the command prompt, and an installation window named "Program Compatibility Assistant" will appear. Now click on "Run program" to proceed with this installation.

Step 4
Now follow the same process which you normally do in an installation. Again choose the existing instance to add SSIS and select Integration Services in the feature selection page which we need to install.

Now this time you can see the installation rule check passes without an error, because we skipped the installation rule process to make this installation possible.

Step 5
Here we can see the "Next" button is enabled, so click on Next to install SQL Server Reporting services on the active node.

Step 6
Once you are done with the installation on the active node (Node 1), follow the same process on each of the other nodes in the cluster.

Step 7

After SSIS installation it will by default point to Default instance, so you need to do below changes.

In the installation folder, You will find the configuration file.

For SQL 2005, by default, this path is: C:\Program Files\Microsoft SQL Server\90\DTS\Binn

For SQL 2008, this is C:\Program Files\Microsoft SQL Server\100\DTS\Binn. In either case, the name of the file is MsDtsSrvr.ini.xml. When first installed, this file will look like this:

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

Step 8

To be able to manage packages on any of those instances, you are going to have to modify your config file. To be able to manage packages on all cluster instances from any one machine, we would make modifications like I did above so that the config file will now look like this:

<?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>Cluster2008r2\SQL2008r2</ServerName>

    </Folder>

    <Folder xsi:type="FileSystemFolder">

      <Name>File System</Name>

      <StorePath>..\Packages</StorePath>

    </Folder>

  </TopLevelFolders>

</DtsServiceConfiguration>

Step 9

So now, whatever machine I put that config file onto will see and be able to manage packages on those cluster machines, just as in the screenshot below, I can see the packages and manage them on those two instances.