Professional Microsoft® SQL Server® 2008 Administration

6.1. A Tour of Integration Services

Certainly the most important Business Intelligence change from SQL Server 2000 to 2008 is found in the component responsible for the movement and manipulation of data. The 2000 platform used Data Transformation Services (DTS), whereas the 2005 and 2008 platforms use Integration Services. The change in capabilities goes far beyond what a simple name change might imply, as Integration Services was a complete ground-up rewrite of DTS. In fact, not a single code line from DTS remains within Integration Services today. You can learn more about upgrading from SQL Server 2000 DTS to SSIS in Chapter 3.

Integration Services is a solution that provides enterprise-level data integration and workflow solutions that have as their goal the extraction, transformation, and loading (ETL) of data from various sources to various destinations. SSIS includes a wide range of tools and wizards to assist in the creation of the workflow and data flow activities that you need to manage in these complex data-movement solutions.

6.1.1. Integration Services Uses

Before diving into the detailed components within Integration Services, you should understand some of the more common business scenarios that involve creating SSIS solutions.

One of the first scenarios is combining data from different sources stored in different storage systems. In this scenario, SSIS would be responsible for connecting to each data source, extracting the data, and merging it into a single dataset. ...