Issue with checkpoints and Event handlers

Hi,

We are currently facing an issue in ensuring restartability of an SSIS package. The scenario is explained below.

Context:

The SSIS Package has two Data Flow tasks.The Data Flow task named DFT1 is the predecessor for DFT2 and chained with OnSuccess precedence constraint.

OnPreExecute and OnPostExecute event handlers have been implemented for DFT1. Each task in both event handlers as well as DFT1 and DFT2 have FailPackageOnFailure set to True.

Scenario1: Task in OnPreExecute of DFT1 fails.

DFT1 is attempted and succeeded.

OnPostExecute of DFT1 was not attempted.

DFT2 was not attempted.

Checkpoint file was created; however, no entries were made.

When restarted, execution started from first step in Control flow.

Scenario2: Task in OnPostExecute of DFT1 fails.

DFT1 and its OnPreExecute Event were executed.

DFT2 was not attempted.

Checkpoint file was created and entries were made.Entries had DTS:result as 0 for OnPreExecute and DFT1 tasks.

When restarted, DFT2 was executed.OnPostExecute event, which failed during previous execution, was not attempted.

Each task in the package, whether it is in Control flow or as part of an event handler is crucial for seamless execution. But apparently, as explained above, there is no reliability on the event handlers in case of failures. Has anyone encountered similar scenario? Is this behavior as per design of the runtime engine?

Thanks, in advance,

Regards,

Rajesh

Rajesh Sridharan wrote:

Hi,

We are currently facing an issue in ensuring restartability of an SSIS package. The scenario is explained below.

Context:

The SSIS Package has two Data Flow tasks. The Data Flow task named DFT1 is the predecessor for DFT2 and chained with OnSuccess precedence constraint.

OnPreExecute and OnPostExecute event handlers have been implemented for DFT1. Each task in both event handlers as well as DFT1 and DFT2 have FailPackageOnFailure set to True.

Scenario1: Task in OnPreExecute of DFT1 fails.

DFT1 is attempted and succeeded.

OnPostExecute of DFT1 was not attempted.

DFT2 was not attempted.

Checkpoint file was created; however, no entries were made.

When restarted, execution started from first step in Control flow.

Scenario2: Task in OnPostExecute of DFT1 fails.

DFT1 and its OnPreExecute Event were executed.

DFT2 was not attempted.

Checkpoint file was created and entries were made. Entries had DTS:result as 0 for OnPreExecute and DFT1 tasks.

When restarted, DFT2 was executed. OnPostExecute event, which failed during previous execution, was not attempted.

Each task in the package, whether it is in Control flow or as part of an event handler is crucial for seamless execution. But apparently, as explained above, there is no reliability on the event handlers in case of failures. Has anyone encountered similar scenario? Is this behavior as per design of the runtime engine?

Thanks, in advance,

Regards,

Rajesh

This is a complicated scenario but I think this is as expected. The only tasks for which the checkpoint is written to are those in the control-flow. In scenario 1 DFT1 has not completed therefore execution will start from there on the second execution. In scenario 2 DFT2 HAS completed therfore execution will start from DFT2 on the second execution.

Tasks in the eventhandler do not affect checkpoint files, now should they. If you have "things" happening in your package that are critical to the execution of the package then they should be in the control-flow.

-Jamie

|||

Thank you, Jamie. Appreciate your response.

Rajesh