SSIS : Retry Extract on connection time.

Requirement: We pull AD Users through web service. The connection often times out. We would like to re-try connecting 3 times, if all three attempts fail continue with the ETL process without abend.

Send an email notification about the connection timeout.

Solution:

Part A

In the Extract Child Package -

 Create a for loop for data extract, which is initialized as follows –

Inside the for loop add on “On Success” and on “On Fail” script tasks apart from Data Flow Task which does the extract.

The “On Success” task sets vContinue variable to 0

The “On Fail” task increments vFail variable, if the loop fails three times, it sets vContinue variable to 0.

Go to On Error event handler for Data Flow task.

Create an empty On Error Event Handler.

Set Propagate system variable value to “False”.

Setting propagate to false will prevent  OnError from bubbling up to parent container.

Add a Send Mail Task with Precedence Constraint of @vFailCount>0 .

The solution so far will prevent the child package from failing and have it re-try 3 times before exiting.

The error will however still propagate up to parent package, to prevent parent package from failing follow the Part B of the solution.

Part B

In the parent package which calls this child package.

Go to the Execute Package Task for this Child Package.

Create an empty OnError Event handler and Set Propagate System variable to False.

End Result:

We have an extract package which connects to web service, if there are time outs it will retry 3 times before exiting. The extract package also sends email about timeout failure.
The extract package is called from a parent ETL package which ignores webservice connection failures and continues with other data load tasks.