Overview of SQL Server Integration Service

Monday, December 7, 2009

What is SSIS
- SSIS is a platform for building high performance data integration and workflow solutions, including the extraction, transformation and load (ETL) operations of data warehousing.
Uses of SSIS
- Populating Data Warehouses and Data Marts
- Merging Data from Heterogeneous Data Stores
- Cleaning and Standardizing Data
- Building Business Intelligence into a Data Transformation Process
- Automating Administrative Functions and Data Loading
SSIS Architecture
- The new integration services architecture is divided into two main sections.

1- Data Transformation pipeline (DTP). - Take the place of the old DTS data pump that was used in SQL server 7 and 200. - Primary function is handle the data flow between the source and target destinations

2 – Data Transformation Runtime (DTR) - A job execution environment that controls the control flow that is used in an SSIS package.

Data Transformation Pipeline (DTP): Overview The DTP takes care of the data flow and transformations that take place as rows are moved between the data source and data target - Each of these components exposes its own distinct object model

DTP uses data adapters to connect to the source and destination data sources.
The DTP engine is accessed using the DTP Pipeline object model.
Data Transformation Runtime (DTR): Overview
The DTR is essentially a job execution environment that controls the control flow that’s used in an SSIS package.

The DTR is accessed using the DTR object framework.
The DTR run-time object framework is the API that supports the integration service import/export wizard and the integration service designer in addition to the command-line dtexe tool.