Chapter 9: Integration Services

Overview

Data Transformation Services (DTS) has been one of the most useful and popular SQL Server utilities since its introduction with SQL Server 7. DTS was originally conceived to be a tool used to perform data extraction, transformation, and loading (ETL) for OLAP Services data warehouses. However, Microsoft quickly realized its usefulness and made DTS the primary tool for importing and exporting data from SQL Server’s relational databases as well as a BI ETL tool. Besides being much easier to use than the old command-line bcp (Bulk Copy Program) tool it replaced, DTS took a big step beyond being a mere data transfer program by providing the ability to transform the data as it transferred it between source and destination endpoints.

DTS offered both a user-friendly wizard designed to perform simple data transfers as well as a graphical designer for more sophisticated data transfer and transformation operations. The SQL Server 7 and 2000 versions of DTS supported 100 percent of OLE DB for the source and target database. This means that although DTS was a part of SQL Server, it could actually be used to transfer data between any two OLE DB data sources without requiring that SQL Server be either the data source or destination. For instance, in addition to being able to import and export data from SQL Server databases, DTS could also be used to transfer data between other database systems such as Access, Oracle, and DB2 without involving SQL Server at all. This kind of flexibility made DTS a very powerful and useful database transfer tool.

However, as cool and useful as DTS was, it still had some rather important limitations. To be enterprise ready, DTS needed better scalability. Plus, DTS packages were not easily transportable between systems. In other words, a DTS package that was designed to perform a transfer from SQL Server system A could not be easily reused to perform that same transfer from system B. In addition to these deployment limitations, the earlier versions of DTS lacked robust error handling and logging, and they had limited manageability.

With SQL Server 2005 Microsoft completely revamped DTS and rewrote it from the ground up. Reflecting its all new nature, Microsoft renamed DTS to Integration Services. Microsoft’s goal for SQL Server 2005’s Integration Services was to make it an enterprise ETL platform for Windows on a par with any of the stand-alone enterprise-level BI ETL products. To those ends, Microsoft wrote the all-new Integration Services using managed .NET code, giving it a more robust foundation. In the process, Microsoft completely redesigned Integration Servces, giving it an all-new architecture, providing better support for programmability and improved run-time performance. The new Integration Services features a new graphical designer and a greatly enhanced selection of data transfer tasks and transformations. Integration Services, like the old DTS, supports 100 percent of the source and target destination, meaning it can independently connect to both the source and destination data sources with no need that either data source be a SQL Server system,. In this chapter you’ll learn about the new features found in SQL Server 2005’s Integration Services. First, you’ll get a look inside the new Integration Services architecture and then take a guided tour of the new Integration Services tools and components.