Data Flow and it’s components in SSIS


What do we mean by Data flow in SSIS?

Data flow encapsulates the data flow engine and consists of Source, Transformations and Target. The core strength of in MS SQL Server Integration Services (SSIS) is its capability to extract data into the server’s memory (Extraction), transform it (Transformation) and load it to an alternative destination (Loading). It means the data is fetched from the data sources, manipulated or modified through various transformations and loaded into the target destination. The data flow task in SSIS sends the data in series of buffer.

Example ScenarioConsider a conveyor belt in a factory. Raw material (Source data) is placed on the conveyor belt and passes through various processes (Transformations). Quality assurance components might reject some material, in which case it can be scrapped (Logged) or fixed and blended back in with the quality material. Eventually, finished goods (Clean and Valid data) arrive at the end of the conveyor belt (Data warehouse).

The first step to implement a Data flow in a package is to add a data flow task to the Control flow of a package. Once the data flow task is included in the control flow of a package, we can start building the data flow of that package.

NOTE: –  Arrows connecting the data flow components to create a pipeline are known as Service paths where as arrows connecting components in control flow are known as Precedence constraints. At design time, Data viewers can also be attached to the Service paths to visualize the data.

STEP 1.  Creating a Data Flow will include:-

Source(s) to extract data from the databases.

  1. Adding Connection managers to connect to the data sources.
  2. Transformations to manipulate or modify the data according to the business need.
  3. Connecting data flow components by connecting the output of source to transformation and the output of transformation to destination.
  4. Destination(s) to load the data to data stores.
  5. Configuring components error outputs.

STEP 2.  What are the Components of Data flow?

Components includes –

  1. Data source(s).
  2. Transformations.
  3. Destination(s).

Component 1  Data Flow Sources

Data Flow Sources Description
OLE DB Source Connects to OLE DB data source such as SQL Server, Access, Oracle, or DB2.
Excel Source Receives data from Excel spreadsheets.
Flat File Source Connects to a delimited or fixed-width file.
Raw File Source Do not use connection manager. It produces a specialized binary file format for data that is in transit.
XML Source Do not use connection manager. Retrieves data from an XML document.
ADO.NET Source This source is just like the OLE DB Source but only for ADO.NET based sources.
CDC Source Reads data out of a table that has change data capture (CDC) enabled. Used to
retrieve only rows that have changed over duration of time.
ODBC Source Reads data out of table by using an ODBC provider instead of OLE DB.

Component 2  Data Flow Transformations

Transformation Categories Transformations
Row Transformations Character Map
Copy Column
Data Column
Derived Column
OLE DB Command
 Rowset Transformations    Aggregate
 Percentage sampling/Row sampling
 Split and Join Transformations      Conditional split
 Look up
 Merge join
 union All
 Business intelligence transformations      Data Mining Query
 Fuzzy Look Up
 Fuzzy Grouping
 Term Extraction
 Term Look up
 Script Transformations  Script
 Other Transformations      Audit
Cache Transform
 Export Column
 Import Column
 Row Count
 Slowly Changing Dimension

Component 3 Data Flow Destinations

Data Flow Destinations Description 
 ADO.NET Destination Exposes data to other external processes such as a .NET application.
 Data Reader Destination Allows the ADO.NET Data Reader interface to consume data, similar to the ADO.NET Destination.
 OLE DB Destination Outputs data to an OLE DB data connection like SQL Server, Oracle or Access.
 Excel Destination Outputs data from the Data Flow to an Excel spreadsheet.
 Flat file Destination Enables you to write data to a comma-delimited or fixed-width file.
 Raw file Destination Outputs data in a binary format that can be used later as a Raw File Source. It’s usually used as an intermediate persistence mechanism.
 ODBC Destination Outputs data to an OLE DB data connection like SQL Server, Oracle or Access.
 Record set Destination Writes the records to an ADO record set. Once written, to an object variable, it can be looped over a variety of ways in SSIS like a Script Task or a Foreach Loop Container.
 SQL Server Destination The destination that you use to write data to SQL Server. This destination has many limitations, such as the ability to only write to the SQL Server where the SSIS package is executing. For example – If you’re running a package to copy data from Server 1 to Server 2, then the package must run on Server 2. This destination is largely for backwards compatibility and should not be used.

This completes the introduction of Data flow and it’s components in SSIS. In our next tutorial we will discuss more about the various transformations categories and there functionalities. I hope this grabs your interest towards Data Flow in SSIS. Your comments are welcome.

VN:F [1.9.22_1171]
Your Feedback hels to Improve
Rating: 9.6/10 (32 votes cast)
VN:F [1.9.22_1171]
Rating: +29 (from 29 votes)
Data Flow and it's components in SSIS, 9.6 out of 10 based on 32 ratings
  • sreeharsha

    Excellent man…. Ur work is really good..Keep it up

  • Hey Sreeharsha,
    I am glad to see your feedback in the form of such a nice comment !!!
    We will be sharing more information, so bookmark us as your favorite site.
    Thanks for motivating us.
    Chander Sharma

  • surender.M

    Very good command on the subject easy to understand.
    Thanks alot for posting these articles.

    • Hi Surender,
      We are happy to see you liked our efforts and took out time to appreciate us.
      We always strive to present information in an easy and better way.
      Stay tune for more bud !!!

      Chander Sharma
      Founder, PhpRing

  • Pingback: Data Flow and it's components in SSIS()