Setup SSIS Cluster

Previous Setup PolyBase Cluster	Manual Configuration	SQL Fixes Install Next

FineBuild can configure a SSIS Cluster. This processing is only relevant if SQL Server is being installed as a cluster.

In most situations SSIS should not be set up as a cluster. SSIS Cluster Guidelines describes when it is safe to set up SSIS as a cluster. If you do decide to cluster SSIS, then FineBuild can do this for you.

FineBuild SSIS Cluster configuration

The SSIS Cluster configuration relates to Process Id 2CCB and is controlled by the parameters below:

SQL Version	Parameter	FULL Build	WORKSTATION Build	CLIENT Build
SQL2019	/SetupSSISCluster:	No	No	N/A
SQL2017	/SetupSSISCluster:	No	No	N/A
SQL2016	/SetupSSISCluster:	No	No	N/A
SQL2014	/SetupSSISCluster:	No	No	N/A
SQL2012	/SetupSSISCluster:	No	No	N/A
SQL2008R2	/SetupSSISCluster:	No	No	N/A
SQL2008	/SetupSSISCluster:	No	No	N/A
SQL2005	/SetupSSISCluster:	No	No	N/A

FineBuild also uses the following parameters to help configure SSIS Cluster:

Parameter	Default	Description
/ClusISSuffix:	IS	Suffix appended to Windows Cluster name for SSIS Clusters

The SQL FineBuild processing for Setup SSIS Cluster consists of the following items:

• Configure SSIS Cluster
• Configure SSIS Configuration File
• Add Node to SSIS Cluster

Top

---

Manual SSIS Cluster Configuration

The following steps show what you would have to do for manual SSIS Cluster configuration. FineBuild does all of this work for you automatically.

The example below uses the disk, server and IP addresses used in Install First SQL Server Cluster Node.

Configure SSIS Cluster

1. Create the SSIS cluster using the following commands

   The SSIS Service Name should be taken from the following table:

   SQL Version	Service Name
   SQL2019	MsDtsServer150
   SQL2017	MsDtsServer140
   SQL2016	MsDtsServer130
   SQL2014	MsDtsServer120
   SQL2012	MsDtsServer120
   SQL2008R2	MsDtsServer110
   SQL2008	MsDtsServer110
   SQL2005	MsDtsServer

   CLUSTER "PDGB01SQLC01" RESOURCE "PDGB01SQLC01IS" ^
    /CREATE /GROUP:"PDGB01SQLC01DB" /TYPE:"Generic Service" ^
    /PROP DESCRIPTION="SQL Server Integration Services"
   
   CLUSTER "PDGB01SQLC01" RESOURCE "PDGB01SQLC01IS" /OFF
   
   CLUSTER "PDGB01SQLC01" RESOURCE "PDGB01SQLC01IS" ^
    /PRIV ServiceName="MSDtsServerxxx"
   
   CLUSTER "PDGB01SQLC01" RESOURCE "PDGB01SQLC01IS" ^
    /ADDDEP:"SQL Network Name (PDGB01SQLC01DBA)"
   

Configure SSIS Configuration File

The SSIS Configuration File must be moved from its location on a single node to a shared disk location.

1. Ensure the SSIS cluster resource is offline by using the following command:

   CLUSTER "PDGB01SQLC01" RESOURCE "PDGB01SQLC01IS" /OFF
   

2. Get the location of the SSIS Configuration File by reading the value of the following Registry Key:

   SQL Version	Registry Key
   SQL2019	HKLM\SOFTWARE\Microsoft\Microsoft SQL Server\150\SSIS\ServiceConfigFile\
   SQL2017	HKLM\SOFTWARE\Microsoft\Microsoft SQL Server\140\SSIS\ServiceConfigFile\
   SQL2016	HKLM\SOFTWARE\Microsoft\Microsoft SQL Server\130\SSIS\ServiceConfigFile\
   SQL2014	HKLM\SOFTWARE\Microsoft\Microsoft SQL Server\120\SSIS\ServiceConfigFile\
   SQL2012	HKLM\SOFTWARE\Microsoft\Microsoft SQL Server\110\SSIS\ServiceConfigFile\
   SQL2008R2	HKLM\SOFTWARE\Microsoft\Microsoft SQL Server\100\SSIS\ServiceConfigFile\
   SQL2008	HKLM\SOFTWARE\Microsoft\Microsoft SQL Server\100\SSIS\ServiceConfigFile\
   SQL2005 32-bit	HKLM\SOFTWARE\Wow6432Node\Microsoft\MSDTS\ServiceConfigFile\
   SQL2005 64-bit	HKLM\SOFTWARE\Microsoft\MSDTS\ServiceConfigFile\

3. Copy the SSIS Configuration File to the shared disk location

   /VolData:\SQLFiles\MSIS./Instance:.Data

   For example J:\SQLFiles\MSIS.MSSQLSERVER.Data

4. Update the registry key from 2) above to hold the new location of the SSIS Configuration File

5. Change the ServerName value to specifically name the current server

   Look in the SSIS XML configuration file for a Node called ServerName. Set the value for this Node to match the current server name

6. Change the StorePath value to show the shared location for SSIS Packages

   Look in the SSIS XML configuration file for a Node called StorePath. Set the value of this Node to /VolData:\SQLFiles\MSIS./Instance:.Data\Packages

7. Put the SSIS cluster resource online by using the following command:

   CLUSTER "PDGB01SQLC01" RESOURCE "PDGB01SQLC01IS" /ON
   

Add Node to SSIS Cluster

Each node in the SQL Server Cluster must be added as a possible owner of the SSIS Cluster Resource

1. Ensure the SSIS cluster resource is offline by using the following command:

   CLUSTER "PDGB01SQLC01" RESOURCE "PDGB01SQLC01IS" /OFF
   

2. Ensure the current server name as a possible owner for the SSIS Cluster Resource:

   CLUSTER "PDGB01SQLC01" RESOURCE "PDGB01SQLC01IS" /ADDOWNER:"PDGB01SQLC01N01"
   

3. Put the SSIS cluster resource online by using the following command:

   CLUSTER "PDGB01SQLC01" RESOURCE "PDGB01SQLC01IS" /ON
   

Copyright FineBuild Team © 2015 - 2021. License and Acknowledgements

Previous Setup PolyBase Cluster	Top	SQL Fixes Install Next