Share via

  • Article

Managing Packages

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

Note

You cannot connect to an instance of the SQL Server 2005Integration Services service from the SQL Server 2008 version of Management Studio. That is, in the Connect to Server dialog box, you cannot enter the name of a server on which only the SQL Server 2005 version of the Integration Services service is running. However, you can edit the configuration file for the service and thereby manage packages that are stored in an instance of SQL Server 2005 from the SQL Server 2008 version of Management Studio. For more information, see Configuring the Integration Services Service.

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 sysssispackages. 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 sysssispackages 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 sysssispackagefolders table in msdb. The folderid and parentfolderid columns in sysssispackagefolders define the folder hierarchy. The root logical folders in msdb are the rows in sysssispackagefolders that have null values in the parentfolderid column. For more information, see sysssispackages (Transact-SQL) and sysssispackagefolders (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\100\DTS. The MSDB folder lists the Integration Services packages that have been saved to the SQL Server msdb database on the server. The sysssispackages 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 Integration Services Packages in 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 Summary page of SQL Server Management Studio, click the Running Packages folder. Information such as the execution duration of running packages is listed on the Summary page. Optionally, refresh the folder to display the most current information.

To view information about a single running package on the Summary page, click the package. The Summary 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.

In This Section

Integration Services icon (small) Stay Up to Date with Integration Services

For the latest downloads, articles, samples, and videos from Microsoft, as well as selected solutions from the community, visit the Integration Services page on MSDN or TechNet:

For automatic notification of these updates, subscribe to the RSS feeds available on the page.