Overview of SQL Server Integration Service

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.
- Each of these components exposes its own distinct object model

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
- 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.