Integration Services Management Enhancements

Microsoft SQL Server 2005 Integration Services (SSIS) introduces several new and enhanced features and tools to help you manage Integration Services packages more effectively.

New Integration Services Service

The Integration Services service is a Microsoft Windows service that manages package storage and displays a hierarchical view of saved packages in SQL Server Management Studio. The service supports packages stored in the msdb database in an instance of SQL Server or in the file system.

For more information about the new Integration Services service, see Integration Services Service and Managing Integration Services Packages.

New Package Management Utility

The dtutil command-line utility manages packages from the command prompt. You can perform tasks such as copying, deleting, moving, and signing packages that are stored in the msdb database, in an instance of SQL Server, or in the file system.

For more information, see dtutil Utility.

New Running Packages List

The Integration Services service displays a list of running packages in SQL Server Management Studio.

For more information, see Integration Services Service.

New Package Logging Options

Integration Services includes rich logging features, including multiple logging providers, a logging schema from which you can choose the type of information to log, and a flexible logging model that supports logging configuration at the package level and the task level.

For more information, see Logging Package Execution.

New Package Restart Capability

A package that fails during execution can now be restarted from a point other than the very beginning of the package. The package can now include checkpoints that let you restart the package from the failed task instead of having to rerun the whole package. By using checkpoints, a package can avoid unnecessarily repeating these lengthy operations:

  • The download and upload of large files.
  • The loading of large amounts of data, such as a bulk insert.
  • The processing of analytic objects.

When the failed package is rerun, Integration Services uses a checkpoint file to determine the location from which to restart the package.

For more information, see Using Checkpoints in Packages.

New Performance Monitoring Features

Integration Services now includes a set of performance counters for monitoring the performance of the data flow engine.

For more information, see Monitoring Performance of the Data Flow Engine.

New Package Configuration Features

Configurations allow you to dynamically update the properties of package objects, and move packages from development to testing to production environments more easily. Integration Services includes the Package Configuration Wizard to assist with configuration management.

For more information, see Package Configurations.

New Package Deployment Utility

Business Intelligence Development Studio includes the ability to create a deployment utility for Integration Services packages. During the process of creating the deployment utility, Business Intelligence Development Studio automatically detects and includes all package dependencies such as the package configurations, making it an ideal mechanism for deploying packages in a production environment. When you run the Integration Services package deployment utility, you can install packages to the msdb database in an instance of SQL Server 2005 or to the file system.

For more information about deployment see Deployment of Packages.

New Integration Services Roles

Administrators can manage access to Integration Services packages by using SQL Server roles for packages stored in the msdb database in an instance of SQL Server. SQL Server 2005 includes a set of preconfigured server roles for Integration Services.

For more information, see Integration Services Roles.

New Package Encryption Options

Integration Services packages can be encrypted with various levels of encryption to protect sensitive data.

For more information, see Setting the Protection Level of Packages.

New Digital Signatures for Packages

Integration Services packages can be digitally signed.

For more information, see Signing Packages with Certificates.

SQL Server 2005 includes a set of tools and features that support managing, editing, running, and migrating Data Transformation Service (DTS) packages from earlier versions of SQL Server. SQL Server Management Studio lists available DTS packages and provides options to modify or execute them. Integration Services also includes the Package Migration Wizard to assist in migrating DTS packages to the Integration Services format.

For more information about package migration, see Migrating Data Transformation Services Packages. For known issues, see Known Package Migration Issues.

Community Additions