Article ID: 942176 - Last Review: March 2, 2009 - Revision: 4.0

Description of the SQL Server Integration Services (SSIS) service and of alternatives to clustering the SSIS service

View products that this article applies to.
System TipThis article applies to a different operating system than the one you are using. Article content that may not be relevant to you is disabled.

On This Page

Expand all | Collapse all

INTRODUCTION

This article describes the Microsoft SQL Server 2005 Integration Services (SSIS) service and the Microsoft SQL Server 2008 Integration Services (SSIS) service. We do not recommend that you cluster the SSIS service. However, this article describes alternatives to clustering the SSIS service.
Back to the top

MORE INFORMATION

The role of the SSIS service

The SSIS service is a Windows service that monitors the SSIS packages that are running. The SSIS service also manages the storage of SSIS packages.

The SSIS service extends the management functionalities in SQL Server Management Studio. If you are a member of the Administrators group, you can perform the following tasks in SQL Server Management Studio:
  • Import SSIS packages.
  • Export SSIS packages.
  • Execute SSIS packages.
  • Stop SSIS packages.
  • View all the SSIS packages that are currently running.
  • Stop all the SSIS packages that are currently running.
If you are not a member of the Administrators group, you can view and stop only the SSIS packages that you execute.

If you only want to design and to execute SSIS packages, you do not have to start the SSIS service. When the SSIS service is stopped, you can run SSIS packages by using the following utilities:
  • The SQL Server Import and Export Wizard
  • The SSIS designer
  • The Execute Package utility (DTExecUI.exe)
  • The DTExec.exe command prompt utility
If you want to list and to monitor SSIS packages in SQL Server Management Studio, you must start the SSIS service. The SSIS service provides the following management capabilities in SQL Server Management Studio:
  • Retrieve an SSIS package from remote storage or from local storage. Then, the SSIS service can locally execute the SSIS package without using SQL Server Agent jobs.
  • Stop the SSIS packages that are running locally or remotely without using SQL Server Agent jobs.
  • Monitor the SSIS packages that are running locally or remotely without using SQL Server Agent jobs.
  • Import and export SSIS packages.

    Note You can also use the Dtutil.exe utility or the SSIS Deployment Wizard to save SSIS packages in SQL Server or in the file system.
  • Manage the locations where an SSIS package can be saved.
  • Customize the folders where an SSIS package can be saved.
  • Stop the SSIS packages that are running when the SSIS service is stopped.
  • View the events that are related to the SSIS service in the event log.
Back to the top

Clustering the SSIS service

We do not recommend that you cluster the SSIS service. If the SSIS service and SQL Server are installed in different resource groups, the SSIS service cannot delegate credentials. Therefore, you cannot use SQL Server Management Studio from a client computer to manage packages that are stored in SQL Server. Additionally, you can only install one instance of SSIS on a computer. You may also encounter other problems when you cluster the SSIS service.

If you must cluster the SSIS service, visit the following Microsoft Developer Network Web site for more information:
http://msdn2.microsoft.com/en-us/library/ms345193.aspx (http://msdn2.microsoft.com/en-us/library/ms345193.aspx)

More information about clusters

In a cluster, the network adapter, the CPU, the memory, and the operating system are redundant. However, the shared disks do not have to be redundant. You can implement hardware solutions to make disks redundant. For example, you can use RAID devices or a System Area Network (SAN) environment to make disks redundant.

The cluster that we mention in this article is in one geographic location. For more information about geographically dispersed clusters, click the following article number to view the article in the Microsoft Knowledge Base:
280743  (http://support.microsoft.com/kb/280743/ ) Windows clustering and geographically separate sites
Back to the top

Alternatives to clustering the SSIS service

Cluster SQL Server Agent

Because SQL Server Agent jobs can run the DTExec.exe utility to execute SSIS packages, you can cluster SQL Server Agent to make sure that SSIS packages run on schedule when a failover occurs. When you cluster SQL Server Agent, SQL Server Agent jobs are available on all nodes. Therefore, the SQL Server Agent job can fail over between the nodes. The metadata of SQL Server Agent jobs is saved in the MSDB database. You can save your SSIS packages in the MSDB database or in a share disk.

Update the MsDtsSrvr.ini.xml file on each node

Instead of clustering the SSIS service, you can update the MsDtsSrvr.ini.xml file on each node so that the file includes the information about one or more instances of virtual SQL Server in a cluster. When you do this, you save your SSIS packages in the MSDB database in SQL Server. Then, you can manage the SSIS packages from all nodes in the cluster.

Note The MsDtsSrvr.ini.xml file is located in the following folder:
%ProgramFiles%\Microsoft SQL Server\90\DTS\Binn
The following is an example of an MsDtsSrvr.ini.xml file that includes the information about two instances of virtual SQL Server in a cluster. The two instances of virtual SQL Server are named instances.
<?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>ServerA\SQL_Instance1</ServerName>
</Folder>
    <Folder xsi:type="SqlServerFolder">
      <Name>MSDB</Name>
      <ServerName> ServerB\SQL_Instance2</ServerName>
</Folder>
    <Folder xsi:type="FileSystemFolder">
      <Name>File System</Name>
      <StorePath>..\Packages</StorePath>
    </Folder>
  </TopLevelFolders>  
</DtsServiceConfiguration>
If you do not start the SSIS service, SQL Server Agent can still execute maintenance plans.

In the release version of SQL Server 2005 Integration Services, you must install the SSIS service if you want to run the Maintenance Plan Wizard. This problem is resolved in SQL Server 2005 Service Pack 1 (SP1). For more information about this problem, click the following article number to view the article in the Microsoft Knowledge Base:
909036  (http://support.microsoft.com/kb/909036/ ) Error message when you create a new maintenance plan in SQL Server 2005: "Create maintenance plan failed"
You can use any of the following high availability options against user databases, but not for system databases such as MSDB:
  • Replication
  • Log shipping
  • Database mirroring
Therefore, we do not support using these options to obtain high availability for the SSIS packages that are stored in the MSDB database. Instead of clustering the SSIS service, you can cluster the SQL Server service or the SQL Server Agent service to obtain high availability for SSIS packages.
Back to the top
APPLIES TO
  • Microsoft SQL Server 2005 Workgroup Edition
  • Microsoft SQL Server 2005 Standard Edition
  • Microsoft SQL Server 2005 Developer Edition
  • Microsoft SQL Server 2005 Enterprise Edition
  • Microsoft SQL Server 2008 Standard
  • Microsoft SQL Server 2008 Enterprise
  • Microsoft SQL Server 2008 Developer
Back to the top
Keywords: 
kbsql2005bi kbsql2005as kbprb kbexpertiseadvanced kbtshoot KB942176
Back to the top