Knight's Microsoft SQL Server 2012 Integration Services 24-Hour Trainer

Chapter 55

Configuring the Service

In SQL Server 2005, 2008, and in the SQL Server 2012 package deployment model, you deploy packages 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. To connect to the Package Store, the SSIS service must be running. This service is called SQL Server Integration Services, or MSDTSServer110. There is only one instance of the service per machine or per set of clustered machines.

NOTE Important: This lesson is written for those who are in the package deployment model. If you are in the project deployment model, please skip this lesson.

Though you can run and stop packages programmatically without the SSIS 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 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 ...