New Integration Services Architecture

The new Integration Services architecture is divided into two main sections: the Data Transformation Pipeline (DTP) and the Data Transformation Runtime (DTR). Microsoft split Integration Services into two different pieces mainly to make a clear delineation between data flow and work flow. In the previous versions of DTS, the data flow engine was stronger than the work flow capabilities. This new division essentially makes the work flow portion of Integration Services a first-class component on the same level as the data flow component. The new DTP essentially replaces the old DTS Data Pump that was used in the SQL Server 7 and 2000 versions of DTS. Its primary function is to handle the data flow between the source and target destinations. The DTR is basically a job execution environment that controls the work flow that’s used in an Integration Services package. Each of these components is implemented using its own DLL as well as its own distinct object model that you can program against. In Figure 9-1 you can see an overview of the new Integration Services architecture.

Figure 9-1: Integration Services architecture

The new Integration Services DTP and DTR are discussed in more detail in the following sections. More information about the new Integration Services tool set is also presented later in this chapter.

Data Transformation Pipeline (DTP)

The DTP takes care of the data flow and transformations that take place as rows are moved between the data source and the data target. DTP uses data adapters to connect to the source and destination data sources. As you can see in Figure 9-1, the DTP engine is accessed using the DTP Pipeline object model. This object model is the API that is used by both the built-in transformations supplied by Microsoft and any user-created custom transformations. Transformations move and optionally manipulate row data as they move data from the source columns to the destination columns. You can get a more detailed look at the new DTP architecture in Figure 9-2.

Figure 9-2: Data Transformation Pipeline components

The DTP uses data adapters to connect data source and destination endpoints. As their names suggest, source data adapters connect to the source of the data and provide the input for Integration Services packages. Destination data adapters connect to the data target and output the data. SQL Server 2005 provides a number of source and destination data adapters. Out of the box, SQL Server 2005’s Integration Services comes with a number of built-in source and destination adapters, including adapters for SQL Server, flat files, and other OLE DB–compliant data sources. More information about the specific data adapters that are supported is presented in the section “Integration Services Package Components” later in this chapter.

While the job of the data adapters is to make connections to the data’s source and destination endpoints, the job of the Integration Services transformations is to move and optionally manipulate the data as it’s moved between the source and destination endpoints. Integration Services transformation can be as simple as a one-to-one mapping between the source columns and the target columns, or it can be much more complex, performing such tasks as selectively moving columns between the source and target, creating new target columns using one-to-many mappings, or computing derived columns. SQL Server 2005’s Integration Services comes with a substantial number of built-in transformations which are listing in the section “Integration Services Components” later in the chapter. In addition to these built-in transformations, you can build your own custom transformations by taking advantage of the DTP object model API.

Data Transformation Runtime (DTR)

The DTR consists of the DTR engine and the DTR components. DTR components are objects that enable you to govern Integration Services’ execution. The DTR components are used to build work flows, containers that provide structured operations, tasks that provide data transfer and transformation functionality, and constraints that control the sequence of a work flow in a package. You can see an overview of the new DTR architecture in Figure 9-3.

Figure 9-3: Data Transformation Runtime overview

The primary DTR components are containers and tasks. Tasks are collections of DTR components; each task is composed of data sources and target destinations as well as data transformations. Containers are used to organize and structure related tasks. These containers and tasks are grouped together to form packages. The Integration Services package is the physical unit that groups together all of the functions that will be performed in a given transfer operation. Packages are executed by the DTR to perform data transfers. Integration Services packages can be easily rerun or even moved to a different system and executed stand-alone. More information about the specific components that make up the DTR is presented in the section “Integration Services Package Components” later in this chapter.

The primary purpose of the DTR engine is to control the execution of Integration Services packages. The DTR controls the work flow of the tasks contained in a Integration Services package. In addition, the DTR engine stores package layout; runs packages; and provides debugging, logging, and event handling services. The DTR engine also enables you to manage connections and access Integration Services package variables.

The DTR is accessed using the DTR object framework. The DTR run-time object framework is the API that supports the Integration Services Import/Export Wizard and the Integration Services Designer in addition to the command-line dtexec tool. Both the Import/Export Wizard and the Designer are used to create packages. The DTR engine is written in C++, and its API is exposed using both a native C++ API as well as a .NET assembly that enables it to be accessible to managed .NET applications. Programs that use the DTR object model can automate the creation and execution of Integration Services packages.