SSIS Data Flow Element and its Components

SSIS Data Flow allows tasks to execute in a particular sequence, which has been predetermined by the logic of your SSIS Package implementation. It allows you to have flexibility of SSIS Package execution from start to end. The main objective of the SSIS Data Flow is to manage transformation of the data while it is in transit. In fact, it is responsible for any activity that involves data manipulation.

SSIS Data Flow consists of several component that you can add to the Data Flow design interface.

  1. 1. SSIS Sources are components which you configure via Connection Manager. Each of the configured of built-in source can be reused throughout your packages. There are six, out of the box, sources
    • OLE DB Source is designed to connect to OLE DB compliant data source: SQL, DB2, MS Access, Oracle or Sybase.
    • Raw File Source is designed to retrieve data from binary format file.
    • XML Source is designed to retrieve data from an XML based document.
    • Excel Source is designed to connect and perform data import from MS Excel.
    • ADO.NET Source is recent addition to SSIS and works just like OLE DB but only for ADO.NET base sources.
  2. Destinations are designed to consume data after this data has passed transformation. It is managed through the SSIS Connection Manager. There are 12 out of the box destinations exist in SSIS.
    • Data Mining Model Training analyzes data model
    • Dimension Processing can perform full or incremental dimension refresh
    • Excel Destination brings data into Excel
    • Data Reader Destination allows ADO.NET DataReader interface to consume data
    • Flat File Destination brings data into Flat File
    • OLE DB Destination sends data into OLE DB compliant database such as SQL, DB2, MS Access, Oracle or Sybase.
    • ADO.NET Destination is designed to expose data to external processes such as SSRS, .NET applications and ADO.NET compliant destinations
    • Partition Processing performs update of an SSAS partition
    • Raw File Destination send data to Binary files
    • Recrodset Destination send records to ADO RecordSet
    • SQL Server Destination uses SQL Server as its final destination for the data
    • SQL Server Compact Edition Destination send data to SQL Server compact edition
  3. Transformations designed to change data or transform it as required by the process. Transform components can sort, filter, modify data as well as accept or reject while it’s in the data pipe in-memory. There are several transforms available with SSIS out of the box install

    Transforms

    Description

    Aggregate

    It is designed to aggregate data

    Audit

    It is designed to information in the data pipe

    Character Map

    It is designed to change casing of the data

    Conditional Split

    It is designed to split data based on the condition

    Copy Column

    It is designed to make a copy of any column

    Data Conversion

    It is designed to convert datatypes

    Data Mining Query

    It runs queries against SSAS

    Derived Column

    It creates new column from an expression

    Export Column

    It is designed to export data from Data Flow into a file

    Fuzzy Grouping

    It is designed to clean up data based on fuzzy logic

    Fuzzy Lookup

    It is designed to standardize data based on fuzzy logic

    Import Column

    It reads data from a file

    Lookup

    It does a lookup to be used in the transformation

    Merge

    It is designed to merge data sets into one

    Merge Join

    It uses join to merge two data sets into one

    Multicast

    It send copied data to a different path

    OLE DB Command

    It executes OLE DB command

    Percentage Sampling

    It captures sample data from the DataFlow

    Pivot

    It is designed to pivot data like you would do it in Excel

    Row Count

    It gets row count from the Data Flow

    Row Sampling

    It performs sampling of the data in the DataFlow

    Script Component

    It uses scripting to perform data transformation

    Slowly Changing Dimension

    It is designed to coordinate conditional update in a dimension

    Sort

    It is designed to data in the DataFlow

    Term Extraction

    It is designed to lookup noun or adjectives in data

    Term Lookup

    It is designed to look up a certain word.

    Union All

    It merges two or more datasets

    Unpivot

    It is designed to unpivot data

Featured pages

Architecture

Learn SSIS architecture, key concepts and major SSIS components. Our online tutorials teach you pl…

Development

Learn how to develop SSIS based solutions and application. SSIS development tutorial explains SSIS…

Administration

Find out what is required to install and configure SSIS as well as what administrative task requir…