Integration Services Enhancements
Microsoft SQL Server 2005 Integration Services (SSIS) is a new, highly scalable platform for building high performance data integration solutions, including extraction, transformation and load (ETL) packages for data warehousing. Integration Services replaces Data Transformation Services (DTS), which was first introduced as a component of SQL Server 7.0.
In SQL Server 7.0 and 2000, DTS provided only a limited set of tasks and transformations. It was a challenge for the developer to construct packages with complex and repeating workflows, and to apply different types of column-level transformations to data. The graphical designer that was used to create packages combined the workflow and data flow on a single design surface and offered limited control flow options.
In SQL Server 2005, Microsoft has designed and built a completely new ETL product—SQL Server 2005 Integration Services (SSIS). Integration Services solves many of the difficulties and limitations of DTS. SSIS enhancements include a new extensible architecture, a new package designer, and a multitude of new tasks, looping structures, and transformations and also improvements in package deployment, management, and performance.
Integration Services includes graphical tools and wizards for building and debugging packages; tasks for performing workflow functions such as SQL command execution, FTP operations, and e-mail messaging; data sources and destinations for extracting and loading data; transformations for cleaning, aggregating, merging, and copying data; a management service for administering Integration Services; and application programming interfaces (APIs) for programming the Integration Services object model.
The wide range of enhancements in Integration Services is described in the following table.
| Topic | Description |
|---|---|
Development enhancements include a new architecture that distinguishes control flow from data flow, a new package designer that reflects this distinction, many new and enhanced tasks, data sources and destinations, and transformations, new debugging features, and an extensible API for custom development. |
|
Manageability enhancements include new package storage and management tools, new monitoring and troubleshooting tools, new deployment options, enhanced security features, and legacy support. |