Tag Archives: Taskcentre

Codeless Platforms (Taskcentre) Bug – 0x80040E14

Recently I’ve been receiving this error message when using the “Web Service Connector” and “External Lookup” tool.

Exception from HRESULT: 0x80040E14.

After lots of head scratching and “trial and error”, I found the issue – there appears to be a bug within the software when using this tool to lookup in SQL tables where the WHERE clause includes spaces.

You can work around this in 2 solutions:

  1. Change the lookup table structure to remove spaces from column names
  2. Create a SQL View which renames the columns (without changing original data structure).

With my problem, I went for the former.

BPA (Taskcentre) – Webservice Connector UTF-8 Support

Within the BPA platform, there is a Web Service Connector which allows the calling of web services.

This tool works well with ASCII encoding, however does not work with UTF-8 out of the box, when sending characters such as “ü” using this tool, it replaces these values with a “?”. For example:

In order to support UTF-8, the following registry setting needs adding:

ComputerHKEY_LOCAL_MACHINESOFTWAREWOW6432NodeOrbis SoftwareTaskCentreProviders{F6836170-E8D0-4839-816A-85837B617352}Parameters

This is a string value, and supports the following values:

UTF-8
UTF-7
UTF-16
UTF-32

(I’ve only tested with UTF-8, however have been informed that other values are supported).

If the registry key is missing (or not set) it defaults back to ASCII.

Once updated, no restart of the service is required – it works automatically on next run of the task:

VBScript – Add Working Days to Date

The VBScript “DateAdd” allows you to add days to a date. The Syntax is:

DateAdd(interval,number,date)

For example, to add 4 days, you use:

DateAdd(d,4,Now())

The issue is, this doesn’t include weekends. However I fell across this solution thanks to Sholsinger:

Function BusinessDayAdd(delta, dt)
  dim weeks, days, day
  
  weeks = Fix(delta/5)
  days = delta Mod 5
  day = DatePart("w",dt)
  
  If (day = 7) And days > -1 Then
	If days = 0 Then
	  days = days - 2
	  day = day + 2
	End If
	days = days + 1
	day = day - 7
  End If
  If day = 1 And days < 1 Then
	If days = 0 Then
	  days = days + 2
	  day = day - 2
	End If
	days = days - 1
	day = day + 6
  End If
  If day + days > 6 Then days = days + 2
  If day + days < 2 Then days = days - 2
  
  BusinessDayAdd = DateAdd("d", (weeks * 7 + days), dt)
  
End Function

To use this function, call it as follows:

BusinessDayAdd(4,Now())

BPA (Taskcentre) – “The operation has timed out” when calling a webservice multiple times

An issue I’ve had a few times in the past with regards BPA (formerly “Taskcentre) is the following error message in a specific scenario. The error is caused when a webservice is called multiple times. Usually the first 2 calls are called correctly, however when called a 3rd time, the error message is thrown.

Oddly, when debugging the issue, if you set BPA to use Fiddler to debug the issue, then the error does not happen.

The error message thrown is:

The operation has timed out .

As it doesnt fail on the first 2 calls, this will lead to errors such as:

Step failed, 3 web service calls complete, 1 call(s) failed

In addition the “Connection timeout time” setting on the web service connector is ignored:

To fix the issue, it is a very simple fix. Simply edit the “iwtskrun.exe.config” file, adding the following just before the closing </configuration> tag.

<system.net>
<connectionManagement>
<add address="*" maxconnection="100" />
</connectionManagement>
</system.net>

Server 2019 – Installing Legacy Software Requiring IIS 7

I’ve recently had an issue installing legacy software on Server 2019. This legacy software uses IIS 7 or newer.

Server 2019 comes with IIS version 10.

Unfortunately the installer could not pick up that a version newer than 7 was installed. This is due to how it checked the registry to find the version installed.

For fix this, I amended the following Registry value using RegEdit:

ComputerHKEY_LOCAL_MACHINESYSTEMCurrentControlSetServicesW3SVCParametersMajorVersion

I changed the value from 10 to 9, ran the installation, then changed it back to 10 afterwards.

BPA Custom Message Body in Web Service Tool

Within BPA it is possible to add a custom message body. These are not in the documentation, but are very useful. These functions override the standard operation and allow you to modify the document before it is sent by POST.

(In the screenshot below, the word “TEST” appears before the XML generated as part of the input step.)

Operations need to be wrapped within Curly brackets.

A few examples:

{=ThisOperation.XML}

The XML created from the input data.

{=ThisOperation.JSON}

The JSON created from the input data

{=FunctionCall(ThisOperation.XML)}

Call the function “FunctionCall” passing in the XML Input Data.

{=ThisOperation.Parameters(“ParameterName”)}

The parameter “ParameterName”.

Multiple steps can be wrapped together too, for example:

{=BuildRequest(ThisOperation.XML,”CREATE”,ThisOperation.Parameters(“APIKEY”))}

This calls the “BuildRequest” function, passing in the XML, the text “CREATE” and the input parameter “APIKEY”. The output is wrote into the body of the message.

Mockbin and BPA (Formerly Taskcentre)

I use http://mockbin.org/ to test web service calls, and quite recently have been using it with BPA in order to test integrations when access to a live service is not available.

Recently I’ve had an issue with BPA not picking up MockBin correctly and completely ignoring the data which has been returned via the web service call.

After lots of head scratching, in order to fix this, I had to add the headers of “charset: utf-8”. After adding this, Mockbin works perfectly with BPA.

Example below:

{
  "status": 200,
  "statusText": "OK",
  "httpVersion": "HTTP/1.1",
  "headers": [
    {
      "name": "Content-Type",
      "value": "application/xml"
    },
    {
      "name": "charset",
      "value": "utf-8"
    }
  ],
  "cookies": [],
  "content": {
    "mimeType": "application/xml",
    "text": "<?xml version=\"1.0\" encoding=\"utf-8\" ?>\n<tests>\n<test>\n<status>Example</status>\n</test>\n</tests>",
    "size": 0
  },
  "redirectURL": "",
  "bodySize": 0,
  "headersSize": 0
}

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

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.. لعب قمار اون لاين !

Dynamics NAV 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

SQL Reduce Number of Decimal Places

TaskCentre SQL Performance – One way to improve performance

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.