Execution Tree in SSIS

I have been asked many times, the way we can see Execution Query Plan for Queries in SSMS, Can we see something similar in  SSIS too. Well YES WE CAN, it wont give you factors like I/O, CPU or SubTree Cost but will give you fair idea of path and buffers SSIS engine will create to do the DATA FLOW.

At run time Data Flow Engine divides the Data Flow Task operations into Execution Trees which demonstrate how package uses buffers and threads. These execution trees specify how buffers and threads are allocated in the package.

Each tree creates a new buffer and may execute on a different thread. When a new buffer is created such as when a partially blocking or blocking transformation is added to the pipeline, additional memory is required to handle the data transformation; however, it is important to note that each new tree may also give you additional worker thread.[TechNet]

Let's take an example to show the Execution Tree in the Data Flow Task. I will create a simple Data Flow Task and have two flows in it.

1) Direct transfer of data from SrcEmployee table to DestEmployee table.
2) SrcDepartment to DestDepartment by having one Sort Component in between to sort on DepName.

To see Execution Tree log you will have enable build-in logging and to see log entries add Log Event window from View>OtherWindows.

Now when you run package, SSIS will log an entry for User:PipelineExecutionTrees which discribes the Trees/paths SSIS has created to run the Package.

Message:

Begin Path 0 [Tree 1]
   output "OLE DB Source Output" (11); component "SrcEmployee" (1)
   input "OLE DB Destination Input" (29); component "DestEmployee" (16)
End Path 0

Begin Path 1 [Tree 2]
   output "OLE DB Source Output" (124); component "SrcDepartment" (114)
   input "Sort Input" (147); component "SortDepartment" (146)
End Path 1

Begin Path 2 [Tree 3]
   output "Sort Output" (148); component "SortDepartment" (146)
   input "OLE DB Destination Input" (142); component "DestDepartment" (129)
End Path 2

Happy Extraction and Inception!!