Search code examples
sql-serverssisetlssis-2012

SSIS 2012 - Package Task Run Time Status

In Table [catalog].[execution_component_phases] there is an column called as Phase. The Value of the Phase Column are:

  • PreExecute
  • Validate
  • ProcessInput
  • ReleaseConnection
  • AcquireConnection.

Can someone please suggest which value is saying specific Task in a package is in Running Status.

Is there any value which states task is started but not completed yet.

Regards

Solution

  • Based on the official documentation of [catalog].[execution_component_phases] table:

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

    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.

    Based on my experience i can assume that the Order of the execution phases is:

    1. AcquireConnection : Acquiring the related connections required
    2. Validate : Validating the Task/Component
    3. Pre-Execute
    4. ProcessInput : Processing phase
    5. PrimeOutput : Generating outputs
    6. Post-Execute
    7. ReleaseConnection : Release acquired connections

    In the official documentation they provided the following query to read the time spent in each phase:

    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

    Based on the information above, you can - as example - check whether the current task phase to know if it is still running or not.

    References