SSIS Dataflow Task

  • Article

Overview

The SSIS Dataflow task is the real core of [[articles:SQL Server Integration Services (SSIS)|SSIS]]. It is used to move data between heterogeneous data sources - in layman's terms it moves data from one place to another.

The SSIS Dataflow task differs from all of the other [[articles:SSIS tasks|SSIS tasks]] in that it has a dedicated runtime engine, conveniently called the [[articles:SSIS Dataflow engine|Dataflow engine]]. (In fact the Dataflow task and the [[articles:SSIS Dataflow engine|Dataflow engine]] can be considered synonymous with one another.) The [[articles:SSIS Dataflow engine|Dataflow engine]] is a powerful component, capable of moving data from one place to another very efficiently and thus very quickly. 

Make-up of a Dataflow task

A dataflow is ostensibly a collection of [[articles:SSIS component|SSIS components]], connected together. These components can be one of the Data Flow Sources, Data Flow Transformations, or Data Flow Destinations.
The Data Flow Sources are source components in the Data Flow. The Dataflow Engine can read from these, and put the data to the component's output. A Data Flow Source can be XML, Excel, or a flat file, or any databases which can be queried via OLE DB or ADO.NET. There is a special format, the Raw File Source, which is the native format of Integration Services.
The Data Flow Destinations are the targets of the data. The types of the sources can be found here as well (XML, Excel, flat and raw files, ADO.NET and OLE DB destinations), but there are a few additional types. These are the Data Mining Model Training, the Data Reader Destination, Dimension Processing, Partition Processing, Recordset Destination, SQL Server Compact Destination and SQL Server Destination.
Between its source and destination the data usually needs transformations during an [[articles:Extract Transform Load (ETL)|ETL process]]. There are a lot of transformations implemented already (29 in SQL Server 2008 R2), but if none of these can help in a transformation, the Script Component enables the developer to embed custom C# or VB.NET code to solve the remaining tasks.
For the complete list of available built-in transformations, please visit the following article: [[articles:SSIS: List of transformations|SSIS: List of transformations]].

For the official documentation of the Data Flow Task, see the Data Flow topic in Books Online.