I was about to write about data flow buffer size configuration properties but need to get this one out first so that we can refer to it.
The Data Flow task (internally and in the object model also called the 'Pipeline') logs some pretty interesting information that describe the internal scheduler.
To see these messages, one way is:
1. In a package you want to get detailed pipeline logs on, select the 'Logging…' menu option on the control flow.
2. Check the Data Flow task of interest on the tree on the left. Switch over to Details and select the following events:
BufferSizeTuning; PipelineExecutionTrees; PipelineExecutionPlan; PipelineInitialization
3. Run the package and launch the 'Log Events' window from the Control Flow context menu again.
You'll see a new tool window that'll pop up and it will get filled with some events.
BufferSizeTuning
Example:
Rows in buffer type 3 would cause a buffer size greater than the configured maximum. There will be only 2440 rows in buffers of this type. Next post we will talk about what this means.
PipelineExecutionTrees
Example:
begin execution tree 0 output "Aggregate Output 1" (779) input "Data Conversion Input" (793) output "Data Conversion Output" (795) input "OLE DB Destination Input" (827) end execution tree 0 begin execution tree 1 output "Flat File Source Output" (1087) input "Data Conversion Input" (958) output "Data Conversion Output" (959) input "Lookup Input" (117) output "Lookup Output" (118) input "Sort Input" (470) end execution tree 1 begin execution tree 2 output "Sort Output" (471) input "Multicast Input 1" (668) output "Multicast Output 2" (672) input "OLE DB Destination Input" (643) output "Multicast Output 3" (674) input "Aggregate Input 1" (776) end execution tree 2
We've discussed execution trees in the past… they start from a source or a component with asynchronous output and go on until a destination or another asynchronous output. Looks like I have 3 execution trees in my package.
PipelineExecutionPlan
The plan for my package is too large to paste here. This was hinted at in an earlier post when describing EngineThreads. Soon…
PipelineInitialization
Example:
No temporary BLOB data storage locations were provided. The buffer manager will consider the directories in the TEMP and TMP environment variables. These are typically information messages about the directories and other defaults the pipeline will use during its execution.
Reproduced by kind permission of Ashvini Sharma (Microsoft).