How to design a “chunked” task to process large amounts of rows over multiple task runs

Introduction

This article aims to describe how to design a task that processes a high volume of rows in blocks over multiple task runs.

This would be required if, for example, a task containing a query step is producing 1000 rows and generating a Crystal report per row. An error may occur due to the large number of reports being created within a single task run.

To deal with this we build a task that can process 50 rows at a time. Once the first 50 rows have been completed a new task run is created to process the next 50 and so on until all 1000 rows are completed.

Create a Queue table to log the task events

A database table is required to flag when a row has been processed.This is required to prevent the task from processing the same row more than once. It also allows the task to continue from where it left off when processing the next block of rows during the next task run.

  1. On the TaskCentre server machine download and install a local SQL Server Express instance.
  2. Create a new database.
  3. In the new database create a table named QUEUE with the following fields.
Field NameData TypeComments
EVENT_IDvarcher (200), PRIMARY KEY, NOT NULLPrimary key of our QUEUE table. Populated from the primary key of the external system. A data type of ‘varcher’ with a field length of 200 characters will handle the common case.

If the external system is known, the field type should be aligned with that of the external system.

TASK_IDintThe task ID.
TASK_STATUSbit, NOTNULLUnprocessed = 0, Processed = 1

Create stored procedures to maintain the Queue table

Create two stored procedures using the SQL scripts below. The procedures are used to INSERT and UPDATE the QUEUE table.

— INSERTPROCEDURE —

CREATE PROCEDURE INSERT_EVENT
@PRIMARY_KEY varchar(200),
@TASK_ID int,
AS
BEGIN
INSERT INTO [Events]
(EVENT_ID, TASK_ID, TASK_STATUS)
VALUES
(
@PRIMARY_KEY,
@TASK_ID,
0 -- Unprocessed
)
END

— UPDATE PROCEDURE —

CREATE PROCEDURE UPDATE_EVENT
@PRIMARY_KEY varchar(200),
AS
BEGIN
UPDATE
[Events]
SET
TASK_STATUS = 1 –- Processed
WHERE
EVENT_ID = @PRIMARY_KEY
END

Building the “Chunked” Task

Let’s take, as an example, a TaskCentre task which monitors a sales order system. It obtains a list of all new orders for the day and emails out each order as a PDF report.

Before

Screenshot of the task before the chunking has been applied:

1(1)

The task performs the following actions:

  • Database Query – Returns a recordset.
  • Run Crystal Report – Generates a PDF report from the recordset data, one report for each row.
  • Send Email (SMTP) – Sends an email containing a report.

After

Screenshot of the task after chunking has been applied:

2

The task will now perform the following actions:

Database Query

Returns a recordset that produces a restricted amount of rows. 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’. This will ensure that it only picks up the unprocessed transactions. For example:

SELECT TOP50
OrderID
OrderDate
CustomerName
FROM
TestDB.dbo.SalesOrders INNER JOIN TestDB.dbo.[EVENTS]
ON
TestDB.dbo.SalesOrders.OrderID = TestDB.dbo.[EVENTS].EVENT_ID
WHERE
TestDB.dbo.[EVENTS].TASK_STATUS = 0

Loop Control

Decision step checks the ‘RowCount’ property of the Database query step. The task only continues where ‘RowCount > 0’ (i.e. there are rows to process). This prevents the task from going into an endless loop.

Capture3

INSERT_EVENT

Call Stored Procedure step inserts an unprocessed row into the QUEUE table.

Capture1

Runs Crystal Report

Generates PDF reports from the recordset data, one for each row.

Send Message

Sends an email containing a report.

UPDATE_EVENT

Call Stored Procedure step updates the TASK_STATUS field in the QUEUE table to mark it as processed.

Capture2

TASK API Call

Run VBScript step makes a call onto the Task API to run up a new task run.You will require the ‘Task ID ‘ property of your task to supply to the Task API.

This can be located within the open task as follows: General tab -> Options -> Advanced tab

Capture4

Example script required for the Run VBScript step:

Dim Server, UserName, Password
Dim TCAPI, Session, TaskItem, TID

Const LogonTaskcentre = 1
Const LogonWindows = 2

Server = "localhost"
UserName = "Administrator"
Password = "password"

' ALERT!
' Don’t use "TaskID" as the variable name. You will get invalid property assignment errors if you do.

TID = 564

Set TCAPI = CreateObject("Iwcltcp.TCAPI")
Set Session = TCAPI.Logon(LogonTaskcentre, Server, UserName, Password)
Set TaskItem = Session.OpenTaskItem(TID) ' <- This is the TaskID

If TaskItem.Disabled = False Then

TaskItem.QueueTask

End If

This completes the overview of a task designed to block process rows over a number of separate task runs.

Related Articles

SAP Business One Connector. Add or Update operation causes error: -8006 Resource error Login

Symptom When you call onto the Add or Update operation within the SAP Business One Connector tool you may receive the following exception text in the response XML returned from the connector web service. -8006 Resource error Login Cause Calling onto an Add or…

Read More
BPA-8143