0
Vote

Bug: parent of a loop iteration is not correctly being calculated as the loop container

description

In "execution executale duration.dtsx" we have the following query:
WITH [exec_stats] AS (
SELECT em.[execution_path],SUBSTRING(em.[execution_path],1,LEN(em.[execution_path]) - CHARINDEX('\',REVERSE(em.[execution_path]),1)) AS [parent_execution_path]
FROM ( SELECT distinct em.[operation_id],em.[execution_path] FROM [catalog].[event_messages] em WHERE em.[operation_id] = @ExecutionId
UNION
SELECT es.[execution_id],es.[execution_path] FROM [catalog].[executable_statistics] es WHERE es.[execution_id] = @ExecutionId
) em
)
select * from exec_stats
Problem is that this creates the problem seen in attached "exec_stats_results.PNG" - the parent of \Root\ETLJobStageOrders[2] is incorrectly returned as \Root. The data that causes this a warning message in event_messages that is attributed to the loop iteration (e.g. "ETLJobStageOrders:Warning: SSIS Warning Code DTS_W_MAXIMUMERRORCOUNTREACHED.  The Execution method succeeded, but the number of errors raised (1) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.")
That error message is returned by:
select message,execution_path
from catalog.event_messages
where operation_id = @ExecutionId and execution_path = '\Root\ETLJobStageOrders[2]'
In short, we are incorrectly calculating the parent of a loop iteration when that loop iteration appears in catalog.event_messages.execution_path. This creates the situation that you can see in attached report screenshot  "erroneous_execution_duration_report.png" where a loop iteration incorrectly appears as the child of the root.

file attachments

comments