Knight's 24-Hour Trainer: Microsoft® SQL Server® 2008 Integration Services

Chapter 43. Configuring the SSIS Service

When you deploy your packages, they are stored into what is called the SSIS Package Store. The Package Store in some cases actually physically stores the package, such as the msdb database option. If you're using file system storage, the Package Store just keeps a pointer to the top-level directory and enumerates through the packages stored underneath that directory. In order to connect to the Package Store, the SSIS service must be running. This service is called SQL Server Integration Services, or MSDTSServer100. There is only one instance of the service per machine or per set of clustered machines.

Though you can run and stop packages programmatically without the service, the service makes running packages more manageable. For example, if you have the service run the package, it tracks that the package is executing and people with the proper permission can interrogate the service and find out which packages are running. Those people that are in the Windows Administrators group can stop all running packages. Otherwise, you can stop only packages that you have started.

The service can also aid in importing and exporting packages into the Package Store. This lesson covers other uses for the service, but one last great use for the service worth mentioning at the start is how it can enable you to create a centralized ETL server to handle the execution of your packages throughout your enterprise.

The MSDTSServer100 service is configured through ...