Export (0) Print
Expand All
1 out of 1 rated this helpful - Rate this topic

catalog.execution_component_phases

SQL Server 2012

Displays the time spent by a data flow component in each execution phase.

Column name

Data type

Description

phase_stats_id

bigint

Unique identifier (ID) of the phase.

execution_id

bigint

Unique ID for the instance of execution.

package_name

nvarchar(260)

The name of the first package that was started during execution.

task_name

nvarchar(4000)

The name of the data flow task.

subcomponent_name

nvarchar(4000)

The name of the data flow component.

phase

nvarchar(128)

The name of the execution phase.

start_time

datetimeoffset(7)

The time when the phase started.

end_time

datetimeoffset(7)

The time when the phase ended.

execution_path

nvarchar(max)

The execution path of the data flow task.

This view displays a row for each execution phase of a data flow component, such as Validate, Pre-Execute, Post-Execute, PrimeOutput, and ProcessInput. Each row displays the start and end time for a specific execution phase.

The following example queries the catalog.execution_component_phases view to calculate the active time and the total time for each component in the data flow task, for a given execution of the package (execution ID = 1841). Active_time refers to the total amount of time that a specific package or component has spent executing in all phases. Total_time is the total elapsed time for the package or component.

Caution note Caution

The catalog.execution_component_phases view provides this information when the logging level of the package execution is set to Performance or Verbose. For more information, see Enable Logging for Package Execution on the SSIS Server.

use SSISDB
select package_name, task_name, subcomponent_name, execution_path,
    SUM(DATEDIFF(ms,start_time,end_time)) as active_time,
    DATEDIFF(ms,min(start_time), max(end_time)) as total_time
from catalog.execution_component_phases
where execution_id = 1841
group by package_name, task_name, subcomponent_name, execution_path
order by package_name, task_name, subcomponent_name, execution_path

This view requires one of the following permissions:

  • READ permission on the instance of execution

  • Membership to the ssis_admin database role

  • Membership to the sysadmin server role

Note Note

When you have permission to perform an operation on the server, you also have permission to view information about the operation. Row-level security is enforced; only rows that you have permission to view are displayed.

Did you find this helpful?
(1500 characters remaining)
Thank you for your feedback

Community Additions

ADD
Show:
© 2014 Microsoft. All rights reserved.