TaskCentre How to: Design a task to have resilience

This article is from the Orbis Knowledgebase:

Introduction

TaskCentre has the ability to interact with multiple systems effectively linking them together. Because of this, there exists a need to treat all of the steps within a task as a single transaction.   For example, a task that reads data from Database A, writes that data to Database B and sends an email notification to Mail Server C would ideally be handled as a single transaction.

In practice treating this as a single transaction would be difficult to achieve. (Think about it, if the email notification to Mail Server C failed we would have to roll back the update to Database B  - not an easy task to accomplish when we don’t have access to the database transactions statements; BEGIN TRANSACTION, COMMIT and ABORT.

Summary

This article is intended to provide information how to make tasks resilient by employing a retry mechanism to reprocess failed transactions.

Example Task

Let’s take as an example a TaskCentre task which monitors a sales order system and emails a report containing details of a newly placed order. The ‘Process Orders’ task contains the following four steps.

SQL Trigger Event - Maps the OrderID to a task variable.

Database Query - Extracts additional details relating to the OrderID such as the OrderDate, CustomerName, and OrderAmount

Format HTML Report - Creates a HTML formatted report from the query data.

Send Message - Emails out the report.


Screenshot of  the ‘Process Orders’ task
 

The problem with this task is that it is not resilient to failure. The following two failures could occur after the SQL Trigger event step has fired
1. The Database Query step times out because the database server is busy.
2. The Send Message step fails because the SMTP server is unreachable.

Either of these issues will cause the trigger event to be ‘lost’ because no report will ever be sent for that order. To manage this we will build a retry mechanism which will have the following two features:

1. Events Queue  - Places the event to be processed in a queue.

2. Recovery  - Retry when a failure has occurred.

1. Creating the Events Queue

The principal here is that an event will be written to the EVENTS table at the start of the task run. The status flag will initially be set to a status of ‘Unprocessed’.  Only if all the steps succeed and it gets to the final step is the entry updated with a status of ‘processed’

1. 1 Create an Events table to queue events

1. On the TaskCentre server machine install a local SQL Server Express instance.
2. Create a table called EVENTS and add the following columns.
 

Field Name DataType Comments
EVENT_ID Set the data type to match that of the external system’s primary key Primary key of our EVENTS table. Populated by the primary key from the external system. 
TASK_ID int The task id
TASK_STATUS bit, NOT NULL Unprocessed = 0, Processed = 1

 

CREATE TABLE [EVENTS]
(
  -- Assumes the external system’s primary key is char(20)
  EVENT_ID char(20) NOT NULL,

  -- The Task ID is found under the ‘Advanced’ tab of ‘Task Options’.
  TASK_ID int NULL,
 
  -- Unprocessed = 0, Processed = 1
  TASK_STATUS bit NOT NULL
 
  CONSTRAINT PK_EVENTS PRIMARY KEY CLUSTERED (EVENT_ID)

 

1.2 Task design changes

Edit the task to add an 'OLEDB Call Procedure' step named ‘INSERT_EVENT’ that inserts a row into the EVENTS table and sets the  TASK_STATUS field as unprocessed.
 

Screenshot of the INSERT_EVENT step parameter mapping pane.
 

The Procedure that the INSERT_EVENT step calls  is as follows:

CREATE PROCEDURE INSERT_EVENT
  @PRIMARY_KEY char(20),
  @TASK_ID int,
AS
BEGIN
  INSERT INTO
[Events]
    (PRIMARY_KEY, TASK_ID, TASK_STATUS)
  
  VALUES
  (
    @PRIMARY_KEY, @TASK_ID, 0 -- Initial state is always ‘Unprocessed’
  )
END

2. Processing the Events Queue

2.1 Task design changes

Edit the task further with the addition of an OLEDB Call Procedure step named ‘UPDATE_EVENT’ that updates the TASK_STATUS field in the EVENTS table to mark it as processed.
 

Screenshot of the UPDATE_EVENT step parameter mapping pane

 

The Procedure that the UPDATE_EVENT step calls  is as follows:

CREATE PROCEDURE UPDATE_EVENT 
  -- If we’re calling this procedure it means that all
  -- of the preceding task steps have completed
  -- and the status can be set to ‘Processed’

  @PRIMARY_KEY char(20),
AS
BEGIN

  UPDATE [Events]
 
  SET TASK_STATUS = 1 –- ‘Processed’
  
  WHERE PRIMARY_KEY = @PRIMARY_KEY

END 

Screenshot of the final task design. (Note  the additional INSERT_EVENT and UPDATE_EVENT steps compared  to the non-resilient task in the first section.

 

 3. Recovery

Lets assume that  a failed transaction has occurred. How do we recover from it.

The sceenshot below of SQL management studio  shows TaskID 1138 has run five times. The fourth time it ran (EventID 23) one of the steps has  failed.

 
 

3.1 Reprocess failed transactions

To recover from the failure we build a new scheduled task ‘Reprocess Orders’ . This task is the same as the original ‘Process Orders’ task but with the following differences. 

1. The SQL event step has been replaced by a schedule event step set to a regular interval one hour.

2. The SQL in the query step has been amended to JOIN the Orders table to our Events table with a WHERE clause of ‘TASK_STATUS = 0 so that it only picks up the unprocessed transactions.

SELECT
 OrderID
 OrderDate
 CustomerName

FROM
 TestDB.dbo.SalesOrders INNER JOIN TestDB.dbo.[EVENTS]

ON 
 TestDB.dbo.SalesOrders.OrderID = TestDB.dbo.[EVENTS].EVENT_ID

WHERE 
-- Filter on ‘Unrocessed’ rows
 TestDB.dbo.[EVENTS].TASK_STATUS = 0

3. OLEDB Call Procedure step that updates the TASK_STATUS field in the EVENTS table to mark it as processed.
 

 

 Screenshot of  the ‘Reprocess Orders’ task

 

comments
0