SSIS - What Is Data Flow Task In SSIS Package

Introduction

Data Flow Task is one of the most used Task in SSIS Packages. As from the name we can tell that the purpose of this Task is to flow (load) the data from different type of sources to different type of destinations. If you are using SSIS 2012, you will see this task under Favorites by default. You can always move this Task to Common, Other Tasks etc.  Tabs.

Fig 1: Data Flow Task in SSIS Package

When you Drag the Data Flow Task to Control Flow Pane and open it by double clicking or even by clicking Data Flow Tab right next to Control Flow. The Items in SSIS Tool Box change.

Fig 2: Data Flow Tab

Data Flow Task has different type of Tool Box Items. You can divide them in three different categories

• Sources
• Transformations
• Destination

Fig 3: Data Flow Task Tool Box Items

Sources:

Data Flow Task has different type of connectors those can be used to connect to different type of data sources such as Flat File source, Excel source, OLE DB Source, ADO Net Source, XML , ODBC etc.

Transformations:

There are different type of transformations  available in Data Flow Task to use. To perform different logic on your incoming data from source/s , you can use different types of transformation as per your requirements.

Here are few of most common used

Derived Column Transformation:

This transformation can be used to add a new column or implement different logics on incoming data. We can write expressions in Derived Column Transformation and make sure of variable and incoming columns.

Aggregate Transformation: 

Can be used to perform Aggregate functionality on incoming data.

Merge Join Transformation:

Can be used to join incoming data streams from different sources.

Multicast Transformation:

Can be used to divide incoming stream data to multiple outputs.

Conditional Split Transformation:

Can divide the data into multiple outputs depending upon incoming data values. Different logics can be written to generate data branches depending upon incoming data values.

Union All Transformation:

This transformation can be used to merge data coming from multiple data sources.

There are so many others those are available in Data Flow Task for you to get benefit from them while designing your SSIS Package.

Destination:

There are varieties of destinations available those can be used to load data into different type of destinations such as Flat File Destination, Excel Destination, OLÉ DB Destination, Raw File Destination, Excel Destination, ADO NET Destination, SQL Server Destination, ODBC destination etc.

Data Flow Task is commonly used Task but it does not mean that you have to have this Task in your SSIS Package. There could be scenarios/requirements in which you do not need Data Flow Task in your SSIS Package at all. Let's say if you want to move files from one folder to another folder you do not need Data Flow Task or if you are looping through some records and sending email by using script task or Execute SQL Task then you do not need Data Flow Task. Data Flow Task is only required where you need to read some data from some source and load into some destination table/file etc.