Retry a Task on failure

Case
I have a webservice task that sometimes fails (due an external cause) and I want to retry it a couple of times before failing the entire package. How do I create a retry construction for a task?

Solution
A solution could be to add a FOR LOOP around the task you want to retry. This works for all tasks, not just the webservice task.

A For Loop Container for retrying a task

1) Variables
We need a couple of variables for the For Loop Container.
- RetryMax: an integer indicating the maximum number of attempts
- RetryCounter: an integer to keep track of the number of attempts
- QuitForLoop: a boolean for quiting loop before reaching the maximum number of attempts
- RetryPause: an integer for storing the number of pause seconds before retry


Variables for the FOR LOOP

2) For Loop
Add a For Loop Container and move the task you want to retry inside the container. Edit the For Loop Container and set the following properties:

InitExpression: @[User::RetryCounter] = 1
This will give the RetryCounter an initial value.

EvalExpression: @[User::RetryCounter] <= @[User::RetryMax] &&
                             @[User::QuitForLoop] == false
This will indicate when the For Loop Container stops looping. In this case reaching the MaxRetry or when the Boolean variable is filled with True.

AssignExpression: @[User::RetryCounter] = @[User::RetryCounter] + 1
This will increase the RetryCounter.

The FOR LOOP expressions

3a) Pause Task
For this example I will use a Script Task for waiting a couple of seconds/minutes before a retry . If you don't like scripting there are alternatives for a pause. Add a Script Task, give it a useful name and connect it to the task you want to retry. Make sure the Script Task only executes on error by setting the Constraint Option to Failure.

Pause after failure

3b) The Script
Edit the script Task and add the 3 integer variables as read-only variables. Then hit the edit button and copy the contents of my main method to your main method. The example code is in C#. For a VB.Net version you can use this conversion tool.

Read-only variables

// C# Code
using System;
using System.Data;
using Microsoft.SqlServer.Dts.Runtime;
using System.Windows.Forms;

namespace ST_1c11fe9f84ce4662bdc37ece5316e04d
{
    [Microsoft.SqlServer.Dts.Tasks.ScriptTask.SSISScriptTaskEntryPointAttribute]
    public partial class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
    {

        public void Main()
        {
            if (Dts.Variables["RetryCounter"].Value.ToString() != Dts.Variables["RetryMax"].Value.ToString())
            {
                // Fire warning message that the previous task failed
                Dts.Events.FireWarning(0, "Wait", "Attempt " + Dts.Variables["RetryCounter"].Value.ToString() + " of " + Dts.Variables["RetryMax"].Value.ToString() + " failed. Retry in " + Dts.Variables["RetryPause"].Value.ToString() + " seconds.", string.Empty, 0);

                // Wait x seconds
                System.Threading.Thread.Sleep(Convert.ToInt32(Dts.Variables["RetryPause"].Value) * 1000);

                // Succeed Script Task and continue loop
                Dts.TaskResult = (int)ScriptResults.Success;
            }
            else
            {
                // Max retry has been reached. Log, fail and quit
                Dts.Events.FireError(0, "Wait", "Attempt " + Dts.Variables["RetryCounter"].Value.ToString() + " of " + Dts.Variables["RetryMax"].Value.ToString() + " failed. No more retries.", string.Empty, 0);

                // Fail Script Task and quit loop/package
                Dts.TaskResult = (int)ScriptResults.Failure;
            }
        }

        #region ScriptResults declaration
        /// 

        /// This enum provides a convenient shorthand within the scope of this class for setting the
        /// result of the script.
        /// 
        /// This code was generated automatically.
        /// 
        enum ScriptResults
        {
            Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success,
            Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
        };
        #endregion
    }
}

4a) Quit loop on success
To quit the loop when your task executes successfully we are setting the Boolean variable QuitForLoop to true. I'm using a Script Task for it, but you could also use an Expression Task if you're using 2012 and above or a custom Expression Task for 2008.

Add a Script Task, give it a useful name and connect it to the task you want to retry. Edit the Script Task and add the integer variables RetryCounter and RetryMax as read-only variables and the Boolean variable QuitForLoop as read-write variable. After this hit the Edit button and go to the next step.

read-only and read-write variables

4b) The Script
Copy the contents of my main method to your main method. This code will set the Boolean variable to true causing the loop to stop. The example code is in C#. For a VB.Net version you can use this conversion tool.

// C# Code
using System;
using System.Data;
using Microsoft.SqlServer.Dts.Runtime;
using System.Windows.Forms;

namespace ST_a72ebc0827b64c0f8a1083951014129c
{
    [Microsoft.SqlServer.Dts.Tasks.ScriptTask.SSISScriptTaskEntryPointAttribute]
    public partial class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
    {
        public void Main()
        {
            // Fire information message that the previous task succeeded
            bool FireAgain = true;
            Dts.Events.FireInformation(0, "Succeeded", "Attempt " + Dts.Variables["User::RetryCounter"].Value.ToString() + " of " + Dts.Variables["User::RetryMax"].Value.ToString() + " succeeded. Quiting loop", string.Empty, 0, ref FireAgain);

            // Fill boolean variable with true so that the FOR LOOP EvalExpression will evaluate false and quit
            Dts.Variables["User::QuitForLoop"].Value = true;

            Dts.TaskResult = (int)ScriptResults.Success;
        }

        #region ScriptResults declaration
        /// 

        /// This enum provides a convenient shorthand within the scope of this class for setting the
        /// result of the script.
        /// 
        /// This code was generated automatically.
        /// 
        enum ScriptResults
        {
            Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success,
            Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
        };
        #endregion
    }
}

5) Event handler propagate
Now the most important step! A failure on one of the tasks within a parent container (package, sequence, for loop or foreach loop) will also cause the parent container to fail, but we want to continue after an error. The trick is to add an empty OnError event handler on the task that could fail and change the value of the propagate variable .

In this example we want to ignore errors on the webservice task. Go to the event handlers. Select the webservice task as executable and select OnError as eventhandler and then create the event handler by clicking on the link in the middle of the screen: "Click here to create an 'OnError' eventhandler for executable 'WST - Call webservice'.

Next go to the variables pane and hit the Variable Grid Option button to also show system variables (SSIS 2008 has a different button). Then find the system variable Propagate and set it to false.

Last step is to add an annotation in the empty event handler to explain why it's empty.


Empty OnError event handler with propagate set to false

Show system variables for SSIS 2008

Note: propagate will only work within a package not in a parent-child package construction, but there is a workaround available.

6) testing
In the first run the third attempt was successful and the package succeeded. In the second run all five attempts failed and so did the package.

Third attempt was successful

All five attempts failed