The Microsoft® Data Warehouse Toolkit: With SQL Server™ 2005 and the Microsoft® Business Intelligence Toolset

5.1. An Introduction to SQL Server Integration Services

We begin this chapter with an overview of SQL Server 2005 Integration Services. The goal of the introduction is to familiarize you with the ETL toolset so that you can understand its features and grow comfortable with its vocabulary. This overview is not a tutorial on Integration Services; we're focusing more on the "what and why" of the tool than on the "how."

SQL SERVER 2000/2005 CHANGE: DATA TRANSFORMATION SERVICES SQL Server 7.0 and SQL Server 2000 included a product called Data Transformation Services, DTS for short. Many customers used DTS to build part or all of their ETL systems. DTS's primary appeal—perhaps its only appeal—was its price: Included with SQL Server, it was free. The most effective use of DTS was as a framework for an ETL system that was primarily SQL-based. In other words, people mostly wrote SQL statements or scripts to perform transformations, and used DTS to define the success/failure workflow between those scripts. Most DTS-based ETL systems were not substantively different than the old SQL-and-Perl-script custom systems of decades past, except the scripting part was replaced by the DTS model and design palette.

Figure 5.1. The Business Dimensional Lifecycle

The new Integration Services, which replaces Data Transformation Services (DTS), is a lot more than a name change: It's a completely new ...