Mar 8, 2015

Understand execution Tree and configure | enable in SSIS

What is execution Tree in SSIS?

Execution tree is property of data flow task.  It describes how many data buffers need to be allocated to perform data flow task.  Execution tree is group of transformation which start from source adapter or blocking\semi-blocking transformation and end with first blocking\semi-blocking transformation or destination adapters. For examples:

Understand Execution Tree SSIS
Categorization of different component of data flow task:

Source Adapters
Non-Blocking Transformations
Semi-Blocking Transformations
Blocking Transformations
Destination Adapters
ADO NET Source
Audit
Data Mining Query
Aggregate
ADO NET Destination
Excel Source
Character Map
Merge
Fuzzy Grouping
Data Mining Model Training
Flat File Source
Conditional Split
Merge Join
Fuzzy Lookup
DataReaderDest
OLE DB Source
Copy Column
Pivot
Row Sampling
Dimension Processing
Raw File Source
Data Conversion
Unpivot
Sort
Excel Destination
XML Source
Derived Column
Term Lookup
Term Extraction
Flat File Destination

Lookup
Union All

OLE DB Destination

Multicast


Partition Processing

Percent Sampling


Raw File Destination

Row Count


Recordset Destination

Script Component


SQL Server Compact Destination

Export Column


SQL Server Destination

Import Column




Slowly Changing Dimension




OLE DB Command




How to count total execution tree:

To count execution tree first we have to categorize all the transformation of data flow components in source adapters, non-blocking transformations, semi-blocking transformations, blocking transformation and destination adapters. Follow below rule:
Start of execution tree: Source adapters or Semi-blocking or Blocking transformations
End of execution tree:  Destination adapters or Semi-blocking or Blocking transformations

For example, in below data flow task:

Data Flow task execution Tree
a. Source adapters: ADO NET Source
b. Destination Adapters: ADO NET Destination, ADO NET Destination 1, ADO NET Destination 2
c. Non-Blocking transformations: Multicast, Derived Column
d. Semi-blocking transformations: Union All
e. Blocking Transformations: Aggregate, Sort

In the above data flow task has total 7 execution trees as shown in below diagram:

Count execution Tree SSIS
How to configuration | enable execution tree step by step:

For this we have to configure logging of data flow task for event PipelineExecutionTrees.

Step 1: Right click on control flow task area and click on logging:

Enable execution tree 
Step 2: In configure SSIS Logs window, Click on package (left most) then add any log provider type. In the example we are choosing SSIS log provider for text file.

SSIS Add text log providers
Step 3: Now select only data flow task (left most) then select SSIS log provider for text file.

Configuring execution tree log in data flow task
Click on configuration and browse the path where you want to save log.

Adding text file SSIS execution tree
Then click on details tab.

Step 4: Select only PipelineExecutionTrees event:

Execution tree SSIS pipelineexecutiontrees event
Step 5: Now run your package to get the all execution tree logs. After running it you may get below data in your execution log file:

Begin Path 0
   output "ADO NET Source Output" (134); component "ADO NET Source" (129)
   input "Aggregate Input 1" (145); component "Aggregate" (144)
End Path 0

Begin Path 1
   output "Aggregate Output 1" (146); component "Aggregate" (144)
   input "Multicast Input 1" (449); component "Multicast" (448)
   Begin Subpath 0
      output "Multicast Output 1" (450); component "Multicast" (448)
      input "Derived Column Input" (453); component "Derived Column" (452)
      output "Derived Column Output" (454); component "Derived Column" (452)
      input "ADO NET Destination Input" (220); component "ADO NET Destination" (217)
   End Subpath 0
   Begin Subpath 1
      output "Multicast Output 2" (459); component "Multicast" (448)
      input "Union All Input 1" (461); component "Union All" (460)
   End Subpath 1
   Begin Subpath 2
      output "Multicast Output 3" (464); component "Multicast" (448)
      input "Union All Input 2" (474); component "Union All" (460)
   End Subpath 2
End Path 1

Begin Path 2
   output "Aggregate Output 2" (203); component "Aggregate" (144)
   input "Sort Input" (442); component "Sort" (441)
End Path 2

Begin Path 3
   output "Sort Output" (443); component "Sort" (441)
   input "ADO NET Destination Input" (232); component "ADO NET Destination 1" (229)
End Path 3

Begin Path 4
   output "Union All Output 1" (462); component "Union All" (460)
   input "ADO NET Destination Input" (513); component "ADO NET Destination 2" (510)

End Path 4 

No comments:

Post a Comment