Data Flow Task
Applies To: SQL Server 2016
The Data Flow task encapsulates the data flow engine that moves data between sources and destinations, and lets the user transform, clean, and modify data as it is moved. Addition of a Data Flow task to a package control flow makes it possible for the package to extract, transform, and load data.
A data flow consists of at least one data flow component, but it is typically a set of connected data flow components: sources that extract data; transformations that modify, route, or summarize data; and destinations that load data.
At run time, the Data Flow task builds an execution plan from the data flow, and the data flow engine executes the plan. You can create a Data Flow task that has no data flow, but the task executes only if it includes at least one data flow.
To bulk insert data from text files into a SQL Server database, you can use the Bulk Insert task instead of a Data Flow task and a data flow. However, the Bulk Insert task cannot transform data. For more information, see Bulk Insert Task.
A Data Flow task can include multiple data flows. If a task copies several sets of data, and if the order in which the data is copied is not significant, it can be more convenient to include multiple data flows in the Data Flow task. For example, you might create five data flows, each copying data from a flat file into a different dimension table in a data warehouse star schema.
However, the data flow engine determines order of execution when there are multiple data flows within one data flow task. Therefore, when order is important, the package should use multiple Data Flow tasks, each task containing one data flow. You can then apply precedence constraints to control the execution order of the tasks.
The following diagram shows a Data Flow task that has multiple data flows.
Integration Services provides a set of log events that are available to all tasks. Integration Services also provides custom log entries to many tasks. For more information, see Integration Services (SSIS) Logging and Custom Messages for Logging. The Data Flow task includes the following custom log entries:
|BufferSizeTuning||Indicates that the Data Flow task changed the size of the buffer. The log entry describes the reasons for the size change and lists the temporary new buffer size.|
|OnPipelinePostEndOfRowset||Denotes that a component has been given its end-of-rowset signal, which is set by the last call of the ProcessInput method. An entry is written for each component in the data flow that processes input. The entry includes the name of the component.|
|OnPipelinePostPrimeOutput||Indicates that the component has completed its last call to the PrimeOutput method. Depending on the data flow, multiple log entries may be written. If the component is a source, this log entry means that the component has finished processing rows.|
|OnPipelinePreEndOfRowset||Indicates that a component is about to receive its end-of-rowset signal, which is set by the last call of the ProcessInput method. An entry is written for each component in the data flow that processes input. The entry includes the name of the component.|
|OnPipelinePrePrimeOutput||Indicates that the component is about to receive its call from the PrimeOutput method. Depending on the data flow, multiple log entries may be written.|
|OnPipelineRowsSent||Reports the number of rows provided to a component input by a call to the ProcessInput method. The log entry includes the component name.|
|PipelineBufferLeak||Provides information about any component that kept buffers alive after the buffer manager goes away. If a buffer is still alive, buffers resources were not released and may cause memory leaks. The log entry provides the name of the component and the ID of the buffer.|
|PipelineComponentTime||Reports the amount of time (in milliseconds) that the component spent in each of its five major processing steps—Validate, PreExecute, PostExecute, ProcessInput, and ProcessOutput.|
|PipelineExecutionPlan||Reports the execution plan of the data flow. The execution plan provides information about how buffers will be sent to components. This information, in combination with the PipelineExecutionTrees log entry, describes what is happening within the Data Flow task.|
|PipelineExecutionTrees||Reports the execution trees of the layout in the data flow. The scheduler of the data flow engine uses the trees to build the execution plan of the data flow.|
|PipelineInitialization||Provides initialization information about the task. This information includes the directories to use for temporary storage of BLOB data, the default buffer size, and the number of rows in a buffer. Depending on the configuration of the Data Flow task, multiple log entries may be written.|
These log entries provide a wealth of information about the execution of the Data Flow task each time you run a package. As you run the packages repeatedly, you can capture information that over time provides important historical information about the processing that the task performs, issues that might affect performance, and the data volume that task handles.
For more information about how to use these log entries to monitor and improve the performance of the data flow, see one of the following topics:
The following table lists sample messages for log entries for a very simple package. The package uses an OLE DB source to extract data from a table, a Sort transformation to sort the data, and an OLE DB destination to writes the data to a different table.
Many log events write multiple entries, and the messages for a number of log entries contain complex data. To make it easier to understand and to communicate the content of complex messages you can parse the message text. Depending on the location of the logs, you can use Transact-SQL statements or a Script component to separate the complex text into columns or other formats that you find more useful.
For example, the following table contains the message "Rows were provided to a data flow component as input. : : 1185 : OLE DB Source Output : 1180 : Sort : 1181 : Sort Input : 76", parsed into columns. The message was written by the OnPipelineRowsSent event when rows were sent from the OLE DB source to the Sort transformation.
|PathID||The value from the ID property of the path between the OLE DB source and the Sort transformation.||1185|
|PathName||The value from the Name property of the path.||OLE DB Source Output|
|ComponentID||The value of the ID property of the Sort transformation.||1180|
|ComponentName||The value from the Name property of the Sort transformation.||Sort|
|InputID||The value from the ID property of the input to the Sort transformation.||1181|
|InputName||The value from the Name property of the input to the Sort transformation.||Sort Input|
|RowsSent||The number of rows sent to the input of the Sort transformation.||76|
You can set properties in the Properties window or programmatically.
For more information about how to set these properties in the Properties window, click the following topic:
For more information about programmatically adding a data flow task to a package and setting data flow properties, click the following topic:
Video, Balanced Data Distributer, on technet.microsoft.com.