By Nicole Laurier on Apr 23, 2013 3:17:00 PM
This article is from the Orbis Knowledgebase:
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.
This article is intended to provide information how to make tasks resilient by employing a retry mechanism to reprocess failed transactions.
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.
|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
INSERT INTO [Events]
(PRIMARY_KEY, TASK_ID, TASK_STATUS)
@PRIMARY_KEY, @TASK_ID, 0 -- Initial state is always ‘Unprocessed’
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’
SET TASK_STATUS = 1 –- ‘Processed’
WHERE PRIMARY_KEY = @PRIMARY_KEY
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.
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.
TestDB.dbo.SalesOrders INNER JOIN TestDB.dbo.[EVENTS]
TestDB.dbo.SalesOrders.OrderID = TestDB.dbo.[EVENTS].EVENT_ID
-- 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