Managing Integration Services Packages

Microsoft SQL Server 2005 Integration Services (SSIS) includes the Integration Services service for managing Integration Services packages. The Integration Services service is available only in SQL Server Management Studio.

Integration Services provides two top-level folders for accessing Integration Services packages: Running Packages and Stored Packages. The Running Packages folder lists the packages that are currently running on the server. The Stored Packages folder lists the packages that are saved in the package store. These are the only packages that the Integration Services service manages. The package store can consist of either or both the msdb database and file system folders listed in the Integration Services service configuration file. The configuration file specifies the msdb and file system folders to manage. You might also have packages stored elsewhere in the file system that are not managed by the Integration Services service.

Packages that you save to msdb are stored in a table named sysdtspackages90. When you save packages to msdb, you can also group them in logical folders. The use of logical folders can help you organize packages by purpose, or filter packages in the sysdtspackages90 table. You can create new logical folders by using SQL Server Management Studio. By default, any logical folders that you add to msdb are automatically included in the package store.

The logical folders that you create for grouping packages in msdb are represented as rows in the sysdtspackagefolders90 table in msdb. The folderid and parentfolderid columns in sysdtspackagefolders90 define the folder hierarchy. The root logical folders in msdb are the rows in sysdtspackagefolders90 that have null values in the parentfolderid column. For more information, see sysdtspackages90 (Transact-SQL) and sysdtspackagefolders90 (Transact-SQL).

When you open SQL Server Management Studio and connect to Integration Services, you will see the msdb folders that Integration Services service manages listed within the Stored Packages folder. If the configuration file specifies root file system folders, the Stored Packages folder also lists packages saved to the file system in those folders and in all subfolders.

You can store packages in any file system folder, but they will not be listed in subfolders of the Stored Packages folder unless you add the folder to the list of folders in the configuration file for the package store. For more information about the configuration file, see Configuring the Integration Services Service.

The Running Packages folder contains no subfolders and it is not extensible.

By default, the Stored Packages folder contains two folders: File System and MSDB. The File System folder lists the packages that are saved to the file system. The location of these files is specified in the configuration file for the Integration Services service. The default folder is the Packages folder, located in %Program Files%\Microsoft SQL Server\90\DTS. The MSDB folder lists the Integration Services packages that have been saved to the SQL Server msdb database on the server. The sysdtspackages90 table contains the packages saved to msdb.

To view the list of packages in the package store, you must open SQL Server Management Studio and connect to Integration Services. For more information, see How to: View Packages in the Package Store Using SQL Server Management Studio.

Monitoring Running Packages

The Running Packages folder lists the packages that are currently running. To view information about current packages on the Object Explorer Details page of SQL Server Management Studio, click the Running Packages folder. Information such as the execution duration of running packages is listed on the Object Explorer Details page. Optionally, refresh the folder to display the most current information.

To view information about a single running package on the Object Explorer Details page, click the package. The Object Explorer Details page displays information such as the version and description of the package.

You can stop a running package from the Running Packages folder by right-clicking the package and then clicking Stop.

Managing Package Storage

To organize packages, you can add custom folders to the root package store folders that the Integration Services service lists in its configuration file. By default, the root folders are the File System and MSDB folders. For example, you might want to add to the File System folder a Data Cleaning folder that contains all the packages used for cleaning data. You can add custom folders to custom folders, creating a nested folder hierarchy to suit your needs. The custom folders can be deleted and renamed; however, you cannot rename or delete the root folders that the configuration file specifies. To update the root folders that Integration Services lists, you must update the configuration file.

For more information, see Configuring the Integration Services Service.

Importing and Exporting Packages

Integration Services packages can be saved to either to the msdb database or to the file system. You can copy a package from one storage type to the other by using the import or export feature that Integration Services provides. You can also import a package to the same storage type and give the package a different name, to create a copy of a package. The dtutil command prompt utility can also be used to import and export packages.

For more information, see Importing and Exporting Packages and dtutil Utility.

See Also

Concepts

Integration Services Service

Other Resources

Administering Integration Services

Help and Information

Getting SQL Server 2005 Assistance

Change History