SSIS Data Flow Task

The Data Flow task is the most important and complicated task in a SSIS package. It moves data from sources to destinations and adds transforms between them to update, merge or split data. A Data Flow task can consist of a number of data flows and a data flow can have multiple sources and destinations. If the order of data flows is not significant, Putting the data flows in one task is recommended because at run-time, each data flow can use different CPU of multi-core processors to make the whole process efficiently.

The following is a Data Flow task with multiple data flows. The second data flow contains multiple sources and destinations.

Data Flows In Task

In the following example, I'm going to continue work with the package DataFlowTask.dtsx to create a Data Flow task. we'll use the flat file as a data source and a SQL Server table as the destination.

  1. Open the package DataFlowTask.dtsx in the project LearnSSIS2 if it is closed.

  2. Drag & drop the Data Flow Task into the Control Flow of the package then click "Data Flow" tab.
  3. [Data Flow Task]

  4. Drag and drop Source Assistant into the Data Flow editor to open Source Assistant setting window. Click Flat File and select "Flat File Connection Manager" which was just created. Then click OK button.
  5. [Data Flow Source Assistant]

    The Flat File Source was created in the Data Flow design panel. Alternatively, you can drag and drop Flat File Source from Other Sources in the Toolbox into design panel directly.

  6. Drag and drop OLE DB Destination into Data Flow design panel. Then select Flat File Source and put its dark blue arrow onto OLE DB Destination.
  7. [Link to OLE DB Destination]

  8. Right-click OLE DB Destination and choose "Edit..." to open OLE DB Destination Editor. Then Click "New..." and choose "localhost.Test".
  9. [Configure OLE DB Connection Manager]

    If localhost.Test was not created before, you can click "New..." to create one.

  10. Click OK button to close the OLE DB Destination Editor. Then choose student table in the table or the view drop-down list.
  11. [OLE DB Connection Manager]

  12. Click "Mappings" tab and in the Input Column, choose "Column 0" for name and "Column 1" for age.
  13. [OLE DB Connection Manager Column Mappings]

  14. Click OK button. Then Run the package.
  15. [Data Flow Task Running Result]

    From the result, you can see 3 rows were added. Check the student table in the Test database, you can see the 3 records were inserted.

    [Database Result]

In this section, we simply created a Data Flow Task with only a source and a destination. In the next sections, I'm going to introduce some transformations which are common and broadly used in the Data Flow Task.