SQL Server 2005 Integration Services Architecture

SQL Server 2005 Integration Services consists of a core run-time engine and other executable components to support extract, transform, and load (ETL) processes.

Integration Services’ core component, the Run-time Engine, controls the ETL process workflow (referred to as control-flow in Integration Services language)—that is, the logical steps in the ETL process and the sequence of those steps. The engine runs one or more control-flow tasks, executable objects that encapsulate the units of work in the workflow (which might include, for instance, moving or copying files or sending SQL Server commands to a database), or containers, which encapsulate one or more tasks and provide capabilities such as loops and branches (a container could loop over a task some specified number of times, for instance). During execution, tasks and containers issue events (for example, containers and tasks issue events when they complete or if they fail), which are handled by event handlers.

The Integration Services Data-Flow Task, a special type of task executed by the run-time engine, moves data between data sources and destinations. It uses source adapters to connect to a variety of sources (such as SQL Server—or other—databases, flat files, and XML files) and extract data. Data transformations are configurable, executable objects that process or transform data—data transformation range from simple operations, such as converting a string to Unicode, to more complex. For example, a data-mining transformation can make predictions based on trends in existing data. Finally, destination adapters connect to data destinations and load the transformed data.