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