Search code examples
ssisssis-2012

Capture Run Time status of SPECIFIC Task in SSIS 2012

I have a SSIS package which contain 5 sequence task container and inside that other control flow task. all are running parallel.

i am trying to have all audit and metadata information like package name,task name and current status of task (running,completed or failed.)

The Bigger Problem i feel here is how to handle once the status of the task got changed.

this information i am trying to save in sql table

package name i can get from system variable, how to get current status of Task.

i am trying to store status value like "start","in-progress","end".

May someone please share your thoughts.

Thanks

Solution

  • You can capture the change of a task's state by using SSIS's Event Handlers. To do this, click on the task itself and then click on the Event Handlers tab at the top of the design-time window. This will bring up a blank window with a blue link in the middle of the page as follows:

    Control-Flow level Task Event Handles

    Before you click on the blue link, select a specific Event Handler that you want to handle:

    enter image description here

    From here, you can execute specific tasks. For example, you can run an SMTP mail task in the OnError Event Handler to send an e-mail alert regarding a task error. Or, you can run an Execute SQL Task in the OnPostExecute Event Handler to send data to the database once a task has completed.

    I think for your specific problem (how to get Run Time for a task), you will need two Event Handlers:

    1. OnPreExecute: Capture the system's datetime value via GETDATE() and write it to a SQL table as SequenceContainerTaskNameStartTs
    2. OnPostExecute: Capture the system's datetime value via GETDATE() and write it to a SQL table as SequenceContainerTaskNameEndTs

    Where 'Ts' stands for TimeStamp.

    You can then calculate the runtime with the DATEDIFF() function and specify the time interval (e.g., seconds, minutes, hours, etc.). This value can either be stored in a separate field on the table (i.e., SequenceContainerRunTime) or simply generated on-the-fly in a SQL SELECT.

    For more information on SSIS's Event Handlers, read here:

    Integration Services (SSIS) Event Handlers