SSIS execute package task - retry on failure on child package

Question

I have a SSIS package which calls a number of execute package tasks which perform ETL operations

Is there a way to configure the Execute package tasks so that they retry a defined number of times (currently, on the failure of one of the tasks in the child package, the execute package task fails. When this happens, I would like the task to be retried before giving up and failing the parent package)

One solution I know of is to set a flag for each package in the database, set it to a defined value on success and call each package in a for loop container till the flag is successful or the count exceeds a predefined retry count.

Is there a cleaner or more generic way to do this?

makciook makciook · Accepted Answer · 2013-07-19T08:02:12

Yes, put Execute Package Task in a For Loop Container. Define a variable, which will do the count, one for a successrun indicator and a MAX_COUNT Constance. In properties of the Package Task - Expressions, define

FailPackageOnFailure - False

After Execute Task put a Script Task Read/Write Vars: SuccessfulRun, script:

Dts.Variables["SuccessfulRun"].Value = 1

In properties of the For Loop:

InitExpression - @Val_Counter = 0
EvalExpression - @Counter < @MAX_COUNT && @SuccessfulRun == 0
AssingExpression - @Val_Counter = @Val_Counter + 1

Connect PackageTask with ScriptTask using Success line.

OR

In For Loop Container define expression

MaximumErrorCount - Const_MAX_COUNT

But this one hasn't been tested by me yet...

SSIS execute package task - retry on failure on child package

1 Answers