Package Management (SSIS Service)
Management of packages involves tasks including the following tasks:
Monitoring running packages
Managing package storage
Importing and exporting packages
Important
This topic discusses the Integration Services service, a Windows service for managing Integration Services packages. SQL Server 2012 supports the service for backward compatibility with earlier releases of Integration Services. Starting in SQL Server 2012, you can manage objects such as packages on the Integration Services server.
Package Store
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 (SSIS 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 View Integration Services Packages in SQL Server Management Studio (SSIS Service).
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 (SSIS 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 (dtutil.exe) can also be used to import and export packages.
For more information, see dtutil Utility.
Related Tasks
Related Content
|
.gif "Integration Services icon (small)")
Stay Up to Date with Integration Services