Article ID: 942176
Article Last Modified on 11/7/2007
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
INTRODUCTION
This article describes the Microsoft SQL Server 2005 Integration Services (SSIS) service. We do not recommend that you cluster the SSIS service. However, this article describes alternatives to clustering the SSIS service.
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.
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:
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 Windows clustering and geographically separate sites
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 SSIS, 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 Error message when you create a new maintenance plan in SQL Server 2005: "Create maintenance plan failed"
You can use one of the following high availability options against system tables in the MSDB database:
- Replication
- Log shipping
- Database mirroring
However, 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.
Keywords: kbsql2005bi kbsql2005as kbprb kbexpertiseadvanced kbtshoot KB942176
