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

Execute multiple child packages in parallel with loop

Case
I used a foreach loop to execute all my staging packages, but my server isn't using all resources. Is there a way to execute multiple child packages at once, but with a loop?

Executing packages sequentially

Solution
The trick is to use a queue of packages and to have multiple 'processes' taking packages from the queue to execute them. The number of packages that can be executed at a time depends on the complexity of your packages. Staging packages for a single source are not complex, so a good guideline/starting point is to execute one package per processor core.



4 cores => execute 4 child packages at the same time

1) The Queue
For this example I will use a FIFO (first in, first out) queue that is stored in a database table. Alternatives could be the Windows Message Queue or the SQL Service Broker.

-- Create QUEUE table
CREATE TABLE [dbo].[FifoPackageQueue](
 [Id] [bigint] IDENTITY(1,1) NOT NULL,
 [Package] [varchar](50) NULL
) ON [PRIMARY]

GO

-- Add Index on Id
CREATE CLUSTERED INDEX cdxFifoPackageQueue on FifoPackageQueue (Id)

GO

-- Log tabel
CREATE TABLE [dbo].[FifoPackageQueueLog](
 [Id] [bigint] IDENTITY(1,1) NOT NULL,
 [Package] [varchar](50) NULL,
 [StartTime] [datetime] NULL,
 [EndTime] [datetime] NULL
) ON [PRIMARY]

GO

2) Variables and Parameter
For each 'execution line' in my control flow I need one SSIS string variable to store the package name. And I use one Package Parameter to indicate how many execution lines will be active. You can also use an integer variable for that if your SSIS version doesn't have parameters.


String variables, one per execution line

Integer Package Parameter.

3) Fill Queue
In this example I will use an INSERT INTO SELECT query to get all staging packages from SSISDB. You could also use regular INSERT queries or even a Foreach Loop or Script Task that loops through a folder to add packages from the file system to the queue.

INSERT INTO CONV_LOG..FifoPackageQueue
(
     [Package]
)
SELECT      Packages.[name]
FROM        [SSISDB].[internal].[packages] as Packages
INNER JOIN  [SSISDB].[internal].[projects] as Projects
            on Packages.project_version_lsn = Projects.object_version_lsn
WHERE       Projects.name = 'MyProject'
AND         Packages.name like 'STG%';

4) Execution Lines
Each Execution line starts with a Sequence Container connected to the Fill queue task. The Precedence Constraint Expression is @[$Package::ParallelProcesses] >= 1 for the first and @[$Package::ParallelProcesses] >= 2 for the second and so on.

Expression to limit the number of parallel executions

5) Get first package from queue
The Execute SQL Task gets the first package name from the queue and stores it in the SSIS string variable. If the variable is empty then it doesn't continue to the next For Loop.

Output clause is available in SQL 2005

set nocount on;

-- Declare temporary table to store the result of the delete
DECLARE @TmpTable TABLE (Package varchar(50));
-- Declare integer variable for counting the delete result
DECLARE @PackageCount int;

-- Select first record, lock it, delete it and store name in temporary table
WITH cte as (
   SELECT  top(1) Package
   FROM  FifoPackageQueue WITH (rowlock, readpast)
   ORDER BY Id
   )
DELETE FROM cte
output deleted.Package INTO @TmpTable

-- Check if there is 1 record in temporary table
SELECT @PackageCount = count(*) FROM @TmpTable
if @PackageCount = 1
BEGIN
 -- Return package name
 SELECT Package FROM @TmpTable
END
ELSE
BEGIN
 -- Temporary table was empty so queue was empty
 -- Return empty string to stop next precedence constraint
 SELECT '' as Package
END

Store package name in SSIS variable

6) For Loop
The For Loop loops until the package name is empty.

Loop until empty

7) Log starttime
The first Execute SQL Task in the loop inserts the package name and a GETDATE() for the starttime in the log table with an INSERT query. The variable containing the package name is a parameter for this task. A very simple/basic log mechanisme. Adjust it to your needs or remove it if you have an other log mechanism.

INSERT INTO  FifoPackageQueueLog (Package, StartTime)
VALUES   (?, GETDATE())

8) Execute Package Task
Add an expression on the packagename so that it gets replaced with the value of the variable. In the properties of the task set DelayValidation = True. This will prevent errors if your variable is empty.

9) Log endtime
The second Execute SQL Task in the loop logs the enddate with an UPDATE query. The variable containing the package name is a parameter for this task. The start- and enddate will help you choose the optimal number of parallel tasks.

UPDATE  FifoPackageQueueLog
SET   EndTime = GETDATE()
WHERE  Package = ?
AND   EndTime is null

10)  Get next package from queue
This is the exact same task/query as for getting the first package from the queue. If it can't find a package in the queue then it will fill the variable with an empty string and the For Loop will stop.

11) Multiple Execution lines
Repeat steps 4 to 10 an x times. Probably a couple more then you have processor cores in your server. Then start testing to find out the optimal number of parallel tasks.

12) Download example package
For SSIS 2012 I have added an example package for download. It contains 5 execution lines. Add more if you have more cores available. The example is provided for educational purposes only. This example package is not intended to be used in a production environment and has not been tested in a production environment. Test it thoroughly before using it.


Note: you will find a similar solution in the 10 Tips and Tricks for Better SSIS Performance presentation of David Peter Hansen and also in the SQL Rally Amsterdam presentation of Davide Mauri about Automating DWH Patterns Through Metadata. This example package is inspired by their solutions.

How to configure a For Loop Container

How to configure a simple For Loop Container? The For Loop Container is a repeating container that does something untill a condition evaluates true.

Four examples:
- Do something ten times
- Wait untill a certain time
- Wait for a couple of seconds / minutes
- Package Queue

A) Do something ten times
This example does ten times what's within the loop.

1) Variable
Create a integer variable named Counter. This variable is used the count till 10.

Variable Counter (right click in Control Flow)

2) Add For Loop Container
Drag a For Loop Container to your Control Flow and give it a suitable name.

For Loop Container

3) Edit For Loop Container

• Edit the For Loop Container and set the InetExpression to: "@[User::Counter] = 0". This is the initial value of the counter. This example starts at zero.
• Set the required EvalExpression to: "@[User::Counter] < 10". This is the evaluation expression that contains the expression used to test whether the loop should stop or continue. The example stops after 10 times (0, 1, 2, 3, 4, 5, 6, 7, 8, 9).
• Set the requires AssignExpression to: "@[User::Counter] = @[User::Counter] + 1". This is an optional iteration expression that increments the loop counter.

Set the expressions

4) Testing
For testing purposes I added a Script Task with a MessageBox that shows de value of the counter.

Testing the For Loop

B) Wait untill a certain time
This example uses the For Loop as a Wait statement. Let's wait untill 12:20.

1) For Loop Container
Add a For Loop Container to your Control Flow and give it a suitable name. I added a Annotation to clarify that it does nothing within the loop.

Waiting For Loop

2) Edit For Loop Container
Only enter this EvalExpression: GETDATE() <= DATEADD("Mi",20,DATEADD("Hh",12,(DT_DBDATE)GETDATE()))

EvalExpression

Clarification of the expression
It removes the time from the current datetime. So 01-02-2011 12:01:45.002 becomes 01-02-2011 0:00:00.000:
(DT_DBDATE)GETDATE()
After that it adds 20 minutes and 12 hours. So it becomes 01-02-2011 12:20:00.000:
DATEADD("Mi",20,DATEADD("Hh",12,(DT_DBDATE)GETDATE()))
That datetime is compared to the current datetime:
GETDATE() <= DATEADD("Mi",20,DATEADD("Hh",12,(DT_DBDATE)GETDATE()))

3) Testing
For testing purposes I added a Script Task after the loop that shows the current time in a messagebox.

Testing

C)  Wait for a couple of seconds / minutes
With a slightly different approach then example B, you can wait for a couple of seconds, minutes, etc.
Use this expression in the same way as example B to wait 30 seconds:
DATEADD("ss", 30, @[System::ContainerStartTime]) > GETDATE()
or 5 minutes:
DATEADD("mi", 5, @[System::ContainerStartTime]) > GETDATE()

Click here for more information about DATEADD.

Note: Delay techniques with a For Loop causes unnecessary CPU processing. A Script Task with a Thread.Sleep doesn't have that drawback.