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.


  • SSIS Architecture (35177-412-SSIS-Architecture.bmp)
  • DTP (35177-413-DTP.bmp)
  • DTR (35177-413-DTR.bmp)
  • Comments

    Author: Phagu Mahato15 Feb 2014 Member Level: Gold   Points : 5

    Microsoft Integration Services is a platform for building enterprise-level data integration and data transformations solutions. SQL Server Integration Services (SSIS) is a tool that we use to perform ETL operations; i.e. extract, transform and load data. SSIS provides the ability to:

    Retrieve data from just about any source
    Perform various transformations on the data; e.g. convert from one type to another, convert to uppercase or lowercase, perform calculations, etc.
    Load data into just about any source
    Define a workflow
    Creating SSIS packages with SQL Server Management Studio (SSMS)
    Business Intelligence Development Studio (BIDS)
    Creating a simple SSIS package in BIDS
    Deploying SSIS packages
    Executing SSIS packages

  • Do not include your name, "with regards" etc in the comment. Write detailed comment, relevant to the topic.
  • No HTML formatting and links to other web sites are allowed.
  • This is a strictly moderated site. Absolutely no spam allowed.
  • Name: