Description of the SQL Server Integration Services (SSIS) service and of alternatives to clustering the SSIS service
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.
The role of the SSIS serviceThe 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 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
- 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 serviceWe 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 clustersIn 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 AgentBecause 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 nodeInstead 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\BinnThe 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.
If you do not start the SSIS service, SQL Server Agent can still execute maintenance plans.
<?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>
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 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:
- Log shipping
- Database mirroring
Article ID: 942176 - Last Review: 03/02/2009 17:20:27 - Revision: 4.0
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
- kbsql2005bi kbsql2005as kbprb kbexpertiseadvanced kbtshoot KB942176