Table of Contents
- 1 Introduction
- 2 Create a Queue table to log the task events
- 3 Create stored procedures to maintain the Queue table
- 4 Building the “Chunked” Task
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.
- On the TaskCentre server machine download and install a local SQL Server Express instance.
- Create a new database.
- In the new database create a table named QUEUE with the following fields.
|Field Name||Data Type||Comments|
|EVENT_ID||varcher (200), PRIMARY KEY, NOT NULL||Primary 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_ID||int||The task ID.|
|TASK_STATUS||bit, NOTNULL||Unprocessed = 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.
Screenshot of the task before the chunking has been applied:
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.
Screenshot of the task after chunking has been applied:
The task will now perform the following actions:
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
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.
Call Stored Procedure step inserts an unprocessed row into the QUEUE table.
Runs Crystal Report
Generates PDF reports from the recordset data, one for each row.
Sends an email containing a report.
Call Stored Procedure step updates the TASK_STATUS field in the QUEUE table to mark it as processed.
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
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.
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