Tag Archives: Taskcentre

Taskcentre – Recording Success / Failure using “Call Task” Tool

The “Call Task” tool in Taskcentre is a useful tool in calling other tasks.

The task can run in two modes:

Synchronous
Queue

Queue
This adds the task to the queue, to be scheduled at a future point in time

Synchronous
This queues the task and awaits a response.

Sometimes in Taskcentre you wish to know if a task you have called has failed. This is useful in cases where there’s a queue of objects which require some work completing and you are processing these in a batch. Examples include:

Invoices to send
Sales Orders to process
Records require uploading to website
etc

The issue with calling tasks Synchronously is that you cannot easily tell if the task being run has failed or not, however by analysing the output of the task, you can record this information and make decisions accordingly.

Here’s the output of calling this task:

<DataOutput>
	<OutputData>
		<SupplementaryReference />
		<TaskRunStatus>2</TaskRunStatus>
		<Folder>Tasks\Test\Looping options\</Folder>
		<TaskID>383</TaskID>
		<fileName>PO128.doc</fileName>
		<filePath>C:\Temp\File_Test</filePath>
	</OutputData>
</DataOutput>

The “TaskRunStatus” flag is the important part here. If the value is “2”, then the task completed successfully. Value “4” is a failure.

Using the “XML To RecordSet” tool, each results from the “Call Task” can be mapped to a Recordset:

From there, we can then use the “VBScript” tool to loop around all the RecordSet entries:

Here’s the VBScript:

If ThisStep.RecordSource("OutputData_TaskRunStatus") = 2 Then
  Variables("SuccessCount") = Variables("SuccessCount") + 1
Else
  Variables("FailCount") = Variables("FailCount") + 1
End If

From there, you can use these variables within your other steps. For example, here’s a HTML document which is posted to HipChat using code here.

Variables("Message") = "<B>Task Completion</B>"
Variables("Message") = Variables("Message") & "<BR/>Success: " & Variables("SuccessCount")
Variables("Message") = Variables("Message") & "<BR/>Fail: " & Variables("FailCount")

SQL Remove Trailing Decimal Places

Leave a reply

Within a recent project, I’ve been extracting data from Microsoft Dynamics NAV using Taskcentre.

Within NAV, decimals are stored as decimal(38,20), complete will all 20 decimal places..!

The following SQL query is very handy at removing the un-required decimal places (but keeping the accuracy of the data)

SELECT CONVERT(DOUBLE PRECISION, 1.99990000000000000000)
	,CONVERT(DOUBLE PRECISION, 2.00000000000000000000)
	,CONVERT(DOUBLE PRECISION, 3.00005077000000000000)

The results of the above query is:

1.9999
2
3.00005077

TaskCentre SQL Performance – One way to improve performance

Leave a reply

When using the “ODBC” or “OLEDB” tools in TaskCentre, it may appear that a single query is executed on the database. In actual fact from trial and error, I have identified multiple queries are being called.

For example, if you run the following query (This query identifies all Item Ledger Entries, where the timestamp is odd):

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

SELECT TOP 1000 [Item No_]
	,[Posting Date]
	,[Description]
FROM [dbo].[CRONUS UK Ltd_$Item Ledger Entry]
WHERE ([timestamp] % 2) <> 0

Notice, that the transaction isolation level is READ UNCOMMITTED. This means that the query will read the dirty data and not block other users. Also it is limited to 1000 rows.

However when the task is running, it appears that the following SQL queries are run against the database:

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

SELECT TOP 1000 [Item No_]
	,[Posting Date]
	,[Description]
FROM [dbo].[CRONUS UK Ltd_$Item Ledger Entry]
WHERE ([timestamp] % 2) <> 0

and

SELECT COUNT(*)
FROM [dbo].[CRONUS UK Ltd_$Item Ledger Entry]
WHERE ([timestamp] % 2) <> 0

The first query returns the data, the 2nd query returns the number of rows.

Notice in the 2nd query, the transaction isolation level is not declared… (In this case, will cause blocking). Also the TOP statement has been removed too, meaning that all records are counted – not good especially when there is a complex filter such as the above which can be time consuming.

Solution

A solution for this is to disable the counting of the records. One way I have identified how to do this is to UNION to a table returning no rows. (As obviously you don’t want to return rows to corrupt your SQL query).

In this example:

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

SELECT TOP 1000 [Item No_]
	,[Posting Date]
	,[Description]
FROM [dbo].[CRONUS UK Ltd_$Item Ledger Entry]
WHERE ([timestamp] % 2) <> 0
UNION
(SELECT NULL,NULL,NULL WHERE 1<>1)

I’ve identified that when UNION is used, Taskcentre does not count the number of records. This means that if you are using the data, the “RowCount” will return -1 instead of the actual data. If you need this for a further step (such as a decision), then you have to calculate it by looping round in VB as per this knowledgebase article.

Note and Disclaimer:
I don’t have knowledge of how the software actually works internally, so this document is only based on my experience and investigation, so is provided as-is.

Selecting Text Between Characters in Taskcentre – Using VB Script

Leave a reply

In Taskcentre, the text parser tool can be used to extract text between set characters.

A good example is if you have the following input data:

<Result>This is a test</Result>

And wish to extract the following text:

This is a test

This is a good tool, however doesn’t handle errors very well. For example, if the text is not found, it will throw an error message (unless you tell it to ignore errors), which may impact other steps. Additionally the extracted string may be needed in the middle of a Web Service call.

A solution is to use a VBScript function which will handle these errors gracefully. In the example, the function will return a space character if either of the characters are not found. but this can be changed to anything, e.g. “Error”, “String not Found” etc within the function

Using the function is very simple. The screenshot below shows example code:

In the example, the variable Result1 will be set to “This is a test”, whereas Result2 is not found, so is set to a space character.

Full download of the example is available below:
Select String Between Characters Example Script and Task

Automating E-Mail Support Requests using FOAAS and Taskcentre

1 Reply

In this day and age, everyone is looking for prompt responses to e-mails and support requests. In this blog post I’m going to cover integrating FOAAS into e-mail using Taskcentre in order to provide prompt responses to support requests.

By responding quickly to emails, it can greatly improve satisfaction levels, plus also using FOAAS’s technology can also reduce the number of support requests*.

* by increasing the likelyhood of a P45.

In order to integrate the solution, we need to break it down into 3 major steps:

Intercept Email
Call Web Service
Send Response

Intercept E-Mail
In order to intercept the email, we use a standard “SMTP” input trigger. This trigger stores the following into variables:

From E-Mail
E-Mail Subject
E-Mail Body

Additionally, we also need to use the “Text Parser” tool in order to extract the following from the “From E-Mail”

Sender Name
Sender E-Mail

Call Webservice
FOAAS is a REST based webservice. So I’ve built up a web service call, passing through the following parameters:

Sender Name
From Name

This web service returns two values:

message
subtitle

Additionally, the web service requires custom headers which have been amended using the tool

Send Response
A response is then sent to the inbound e-mail automatically, using the following steps:

XML to Recordset (HTML Tool only recognises recordsets)
HTML (To build up an email)
Outbound SMTP

Example
Below you can see an inbound email from a used. As you can see, the email contains both a subject and a request for the support helpdesk. In my VM, the “From” name is “James – Demonstration”, as I use the VM for testing and demonstration purposes.

Here is the response to the email, including the automated response to the web service:

Source Task and Installation
Below is the download link for the example, plus also the web service connector. In order to install the task, the install needs to be done in the following order:

Import Web Services Connector
Import Task

FOAAS Web Service Connector
FOAAS Task

This task has been provided for humour/educational use only.

Taskcentre Alert Notifications – Update

Leave a reply

In a previous post, I mentioned about posting alerts to HipChat.

Unfortunately passing the “notify” parameter resulted in error messages, regardless of the text passed.

It appears that this was a bug with the web service api. I’ve just tried again and passing the following work as expected:

true
false

Stripping all Non Alphanumeric Characters From String in SQL

Leave a reply

With one of the projects I’m currently working on using Taskcentre, I required all non alphanumeric characters stripping from a string in a SQL query (in order to perform an inner join with another table).

With a bit of research I found a brilliant solution here, by Even Mein (Why re-invent the wheel!)

This solution involves creating a function as follows:

CREATE FUNCTION [dbo].[fn_StripCharacters]
(
    @String NVARCHAR(MAX), 
    @MatchExpression VARCHAR(255)
)
RETURNS NVARCHAR(MAX)
AS
BEGIN
    SET @MatchExpression =  '%['[email protected]+']%'

    WHILE PatIndex(@MatchExpression, @String) > 0
        SET @String = Stuff(@String, PatIndex(@MatchExpression, @String), 1, '')

    RETURN @String

END

The function can be called as part of the SQL query as follows:

Alphabetic only:

SELECT dbo.fn_StripCharacters('[email protected]#f4$', '^a-z')

Numeric only:

SELECT dbo.fn_StripCharacters('[email protected]#f4$', '^0-9')

Alphanumeric only:

SELECT dbo.fn_StripCharacters('[email protected]#f4$', '^a-z0-9')

Non-alphanumeric:

SELECT dbo.fn_StripCharacters('[email protected]#f4$', 'a-z0-9')

Plotting Customers on Map from Microsoft Dynamics Nav

Leave a reply

In this blog post, I am going to cover plotting UK customers in a map from Microsoft Dynamics Nav using Taskcentre. In this post I’m using Nav 2013R2, with the Extended Demo Database. (This has a lot more customers and data compared to the standard demo database).

In order to plot the locations on the map, firstly we need to translate the postcodes into Latitude and Longitude. On this Website, there’s a download link to a SQL script to create and populate a table with postcodes within the UK plus the appropriate latitudes and longitudes.

Due to the size of the file, I had problems running the SQL query on my Virtual Machine, so instead used an SSIS job (via the Import/Export data tool on SQL Server). Depending on the amount of RAM available on the SQL server, it should be possible to run the script directly.

Once the file has been imported into the Nav database, I write a SQL query to extract the customer information. Note that in the Postcode table there’s no space between the first half and the second half of the postcode. Therefore using SQL we need to remove the spaces from the postcode table as part of the join.

The join on the two tables is:

REPLACE("CRONUS EXT UK Ltd_$Customer"."Post Code",' ','') = postcodelatlng.postcode

This produces a data set as follows:

Using this data, we can then create a HTML page in order to visualise the data. Firstly we need to use a VBScript step which cycles through each record from the SQL query, storing the data into a variable. This variable will later be used within Taskcentre when building up the Javascript.

After creating this string, this is then combined with two snippets of HTML (stored within variables for ease of editing) to produce a final, complete HTML document. This HTML document is then saved to disk and can be opened in a browser. Using the FTP tools, it could be uploaded to a website if required. The document consists of some JavaScript which uses the Google Maps Javascript API. The principle could be used for any API, such as the Bing Maps API, however personally I do prefer the Google API over Microsoft’s.

The Javascript also uses the “click” event listener. This then shows a dialog box with the customer details when the icon is clicked. This contains just the customer number and name in this example, but could contain information such as full address, contact information, sales order information etc.

Here’s the complete task within Taskcentre:

This task is only a very basic task, but with some imagination and skill, it could be used as a basis for pretty much any use. Especially when you extend the task adding the ability to have custom markers.

Some examples could include:

Trend analysis
Deliveries for a courier
Customers/areas with bad debt/chargebacks

eBook Price Tracking

Leave a reply

Having recently purchased my first eReader, the Kobo Aura, I’ve really started to enjoy reading books more. Instead of having to carry large books about, the small device can sit in my pocket and read books on the go. With the backlight I can even read in bed without the bedside table lamp on.

The only downside is the price of eBooks. Compared to paperbacks at places such as The Works (especially when you take a photo of your receipt and send it to TopCashBack – saving £5 when you spend £20), eBooks can be quite pricey…

There are services out there which will crawl and monitor stores to tell you the prices as these change, such as Luzme, but where’s the fun in that..?

In this post, I’m going to explore crawling the Kobo Store using TaskCentre. Once crawled, any price changes will be updated back to a database and an email to myself sent about the changes.

If a XML Web Service was available, this would make life easy as this could be called directly, but in this case there is not one available, so we need to be creative and download the HTML, extracting the required text.

Database Structure
Firstly we need a database in able to store the data, plus a few books which we wish to keep an eye on.

Here’s the SQL Query to make the table:

CREATE TABLE [dbo].[KoboBooks](
	[UniqueID] [int] IDENTITY(1,1) NOT NULL,
	[Title] [nvarchar](max) NULL,
	[URL] [nvarchar](max) NULL,
	[CurrentPrice] [decimal](18, 2) NULL,
	[PriceLastUpdated] [datetime] NULL,
	[PreviousPrice] [decimal](18, 2) NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

Additionally I’ve added a couple of books to the database which I’m interested in monitoring.

Downloading HTML and Processing
Using a standard OLEDB connection to extract the information from the SQL database, this is passed onto a VBScript step within Taskcentre. This VBScript step uses the WinHttp.WinHttpRequest.5.1 COM Object in order to download the HTML document. Afterwards standard VBScript String Functions are used to extract the required text and stored in a variable.

Additionally, a variable is set to highlight if the price has changed. This is to ensure that only the updated prices are updated back to the database.

Updating Database
In order to update the database, I could write a stored procedure to perform the logic required, but instead I’ve used a lesser used feature in Taskcentre – the memory within the VB Script tool. The memory can be used to call SQL queries and update the data in the database, without writing complex stored procedures. In the screenshot to the left, you can see the mapping, plus also I’ve used the “StoreData” variable to decide whether or not to run the SQL queries to store the data within the database. (See the VB Script in the previous screenshot to see the variable being set.)

Alerting on Price Changes
After storing the data back into the SQL database, this can be extracted via a simple SQL query, dropped into email and sent to myself. To the right is a copy of the data currently stored within the database.

The SQL query to return the data required is:

SELECT  
	KoboBooks.UniqueID AS UniqueID,
	KoboBooks.Title AS Title,
	KoboBooks.URL AS URL,
	KoboBooks.CurrentPrice AS CurrentPrice,
	KoboBooks.PriceLastUpdated AS PriceLastUpdated,
	KoboBooks.PreviousPrice AS PreviousPrice
FROM
	KoboBooks
WHERE
	KoboBooks.PriceLastUpdated >= GetDate() - 1

Putting it all together, here’s a screenshot of the full task:

And a copy of the alert triggered:

Possible Improvements
As with everything, there’s always possible improvements. These could be:

GUI for managing list of books
Multiple Stores (WHSmith etc)
Error handling (If website changes slightly, it will throw error messages)
Using XPath to extract prices instead of simple string functions.
Use SQL triggers to send details of price changes, possibly with a copy of the web page embedded within the email

Using Taskcentre to Post HTML Tables to HipChat

2 Replies

I use Taskcentre from Orbis Software quite a lot to send HTML emails to recipients. These usually are bog standard HTML tables with content. For example, this is a sample report based on Microsoft Dynamics Nav database. This is not the standard out of the box Cronus database, but the extended version. The standard database has very little data (about 600k rows of data), whereas the extended has closer to 2.5 million!

Email has it’s uses, however does have it’s disadvantages, such as:

E-Mail Ping Pong – Emails going backwards and forwards, but not getting anywhere
Users not replying to latest email – 20 minutes into an email conversation, someone else joins in using an earlier email
Too detailed – Sometimes people email a 20 line email, where a single sentence will do

More recently I’ve been trialling HipChat as an alternative to E-Mail. In a previous blog post, I covered posting error messages from Taskcentre and posting in Hipchat – Taskcentre Alert Notifications To HipChat.

Extracting Data From Nav
In order to extract information from Nav, first I need to use either ODBC or OLEDB to return the data. I prefer OLEDB as I’ve seen performance benefits over ODBC, but either is suffice.

Here’s the SQL query as generated by Taskcentre:

SELECT
	dbo."CRONUS EXT UK Ltd_$Salesperson_Purchaser".Name AS Name,
	dbo."CRONUS EXT UK Ltd_$Sales Header"."Salesperson Code" AS "Salesperson Code",
	SUM(dbo."CRONUS EXT UK Ltd_$Sales Line"."Outstanding Amount (LCY)") AS Sales,
	SUM(dbo."CRONUS EXT UK Ltd_$Sales Line"."Outstanding Amount (LCY)"-(dbo."CRONUS EXT UK Ltd_$Sales Line"."Outstanding Quantity"*dbo."CRONUS EXT UK Ltd_$Sales Line"."Unit Cost (LCY)")) AS Profit
FROM
	dbo."CRONUS EXT UK Ltd_$Sales Line",
	dbo."CRONUS EXT UK Ltd_$Sales Header",
	dbo."CRONUS EXT UK Ltd_$Salesperson_Purchaser"
WHERE
	(dbo."CRONUS EXT UK Ltd_$Sales Header"."Document Type" = 1) AND (dbo."CRONUS EXT UK Ltd_$Sales Header".No_ = dbo."CRONUS EXT UK Ltd_$Sales Line"."Document No_" AND dbo."CRONUS EXT UK Ltd_$Sales Header"."Document Type" = dbo."CRONUS EXT UK Ltd_$Sales Line"."Document Type" AND dbo."CRONUS EXT UK Ltd_$Sales Header"."Salesperson Code" = dbo."CRONUS EXT UK Ltd_$Salesperson_Purchaser".Code)
GROUP BY
	dbo."CRONUS EXT UK Ltd_$Sales Header"."Salesperson Code",dbo."CRONUS EXT UK Ltd_$Salesperson_Purchaser".Name

Building HTML for HipChat
Next we need to build the HTML for HipChat. Unfortunately the “Format as Text” tool will not work in this situation (it does not allow outputting of the created text), so we need to use VBScipt instead.

Firstly I’ve created a variable within the task called “TempHTML”. This variable will be used to store the created HTML before posting to HipChat. Then the VBScript step is created which is set to repeat for every row in the returned record set. This is done by checking the box accordingly as per the screenshot.

For clarity, here’s the code used:

' ProcessData Function
' 
Function Create_Table_Rows_Function()
Variables("TempHTML") = Variables("TempHTML") + "<TR>"
Variables("TempHTML") = Variables("TempHTML") + "<TD><B>" + ThisStep.RecordSource("Name") + "</B></TD>"
Variables("TempHTML") = Variables("TempHTML") + "<TD>£" + CStr(Round(ThisStep.RecordSource("Sales"))) + "</TD>"
Variables("TempHTML") = Variables("TempHTML") + "<TD>£" + CStr(Round(ThisStep.RecordSource("Profit"))) + "</TD>"
Variables("TempHTML") = Variables("TempHTML") + "</TR>"
End Function

For those who have HTML skills, you will notice that this does not include any <TABLE> tags, nor column headers. In order to create these, a second VBScript step is created which finishes these off:

' ProcessData Function
' 
Function Create_Headers_Function()
Variables("TempHTML") = "<B>Sales Statistics:</B><BR/><TABLE BORDER=1><TR><TD><B>Salesperson</B></TD><TD><B>Sales</B></TD><TD><B>Profit</B></TD></TR>" + Variables("TempHTML")
Variables("TempHTML") = Variables("TempHTML") + "</TABLE>"
End Function

Posting to HipChat
Posting this to HipChat is very simple, using the HipChat Send Room Notification web service. I’m not going to cover it in this post as it was covered previously, simply use the variable inside the XML Web Service Tool.

Complete Task
Here’s the complete task (I’ve still left in the HTML email example, which is outside of the scope of this blog post):

When the task runs, here’s the posting to HipChat:

Borders
I’ve tried using CSS Table Formatting and even the HTML Table Border Attribute, however neither of these work, so I assume HipChat does not support borders (it does state that only basic HTML is supported and anything unsupported is stripped out). Possibly in future HipChat may support these.

JRevell.com

Johnathan Revell's Personal Website

Tag Archives: Taskcentre

Taskcentre – Recording Success / Failure using “Call Task” Tool

SQL Remove Trailing Decimal Places

TaskCentre SQL Performance – One way to improve performance

Selecting Text Between Characters in Taskcentre – Using VB Script

Automating E-Mail Support Requests using FOAAS and Taskcentre

Taskcentre Alert Notifications – Update

Stripping all Non Alphanumeric Characters From String in SQL

Plotting Customers on Map from Microsoft Dynamics Nav

eBook Price Tracking

Using Taskcentre to Post HTML Tables to HipChat