How do i make a specific component or set of components re-run if they fail in SSIS

Question

Im developing a datawarehouse and all of the things that support and come with it. Recently ran into a problem where my SSIS project, made to update and insert data from a database i have into the datawarehouse, fails. The structure of the SSIS project right now is:

• 5 data flow tasks to update de datawarehouse dimensions and insert new records from the database if there are any
• 3 fact tables : 3 sql tasks used to delete the current day records from the fact tables if the projects get ran more than once a day

Going back to the issue, my data flow taks for the dimensions usually fail once or twice due to "non specified errors".

I would like to know if it is possible for me to make the dimensions components get re-run if any of them fail. I assume it is possible using a for-loop but haven't been able to make it work.

Answer 1

Create a couple of variables that you'll need to control the looping, such as @RetryCount (to hold the current retry number) and @MaxRetries (to define how many times you want to try re-running a failed task).

Then, try to rap the Data Flow Tasks that needs to be re-run inside a For Loop Container. Configure the loop's evaluation expression to check if @RetryCount is less than or equal to @MaxRetries.

Within the For Loop Container, you can set up expressions that will either increment the @RetryCount if a failure occurs or exit the loop if a success occurs. Use the "Expression and Constraint" precedence constraint to achieve this.

In the "For Loop Editor," configure the following expressions:

 - InitExpression: `@RetryCount = 0`
 - EvalExpression: `@RetryCount <= @MaxRetries`
 - AssignExpression: `@RetryCount = @RetryCount + 1`

To configure the Failure and Success Flow, after connecting the Data Flow Task to the next task with an "Expression and Constraint" precedence constraint use the following expressions like @RetryCount <= @MaxRetries to continue the loop on failure, or @RetryCount > @MaxRetries to exit on success.