SSIS - A hosting scenario

  • Article

Issue:

4 instances of SQL Server installed on a 4 node cluster with any SQL Server instance possibly failing over to any other node. All 4 nodes have SSIS installed on them. (I am assuming you have not clustered the SSIS service). You want users to be able to create and store packages  to the SQL Server MSDB database on the cluster and schedule the packages. The users will have SSIS and Client tools installed on their local machines.

Approach:

On the users client machine, the SSIS configuration file needs to be modified to specify the information about the location of the MSDB database. (This file is by default at: C:\Program Files\Microsoft SQL Server\90\DTS\Binn\msdtssrvr.ini) Here is an example:

The names of the SQL Virtual servers here are SQLVirtualServer1 and SQLVirtualServer2. I am assuming you want the client to have the ability to save packages to these two servers. If you want the client to save packages to all 4 SQL instances, then you add the other 2. If you want the client to just use one SQL instance, you only need to specify that.

The ServerName element can contain any name that you want. This dictates what shows up in Management Studio.

<?xml version="1.0" encoding="utf-8"?>

<DtsServiceConfiguration xmlns:xsd="https://www.w3.org/2001/XMLSchema" xmlns:xsi="https://www.w3.org/2001/XMLSchema-instance">

  <StopExecutingPackagesOnShutdown>true</StopExecutingPackagesOnShutdown>

  <TopLevelFolders>

    <Folder xsi:type="SqlServerFolder">

      <Name> VirtualServer1MSDB </Name>

      <ServerName>SQLVirtualServer1</ServerName>

    </Folder>

      <Folder xsi:type="SqlServerFolder">

      <Name>VirtualServer2MSDB</Name>

      <ServerName>SQLVirtualServer2</ServerName>

    </Folder>

    <Folder xsi:type="FileSystemFolder">

      <Name>File System</Name>

      <StorePath>..\Packages</StorePath>

    </Folder>

  </TopLevelFolders> 

</DtsServiceConfiguration>

The key thing to note here is the SSIS is a separate component from the database engine in 2005. So, when the SQL database engine fails over to another node, it will use the SSIS installed on that node. For example, assume you have the configuration below:

Node names      Installed Services

Node A                 Database Engine A, SSIS A

Node B                 Database Engine B, SSIS B

When Database Engine A fails over to Node B, it will use the SSIS install on node B as SSIS itself is not a clustered resource and is a standalone component. The entire management is done from the client machine in your case which always connects to the database engine only since that is where the MSDB database is and so always makes a straight connection to the database engine. So, you will never need to make a connection to a remote Integration Services instance causing delegation failures.