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