Share via

More Strength To BizTalk Messaging Sol. Using XSLT

  • Article

Introduction

In this article, I would like to discuss a design that I have improved during one of my recent project. First, please have a look at the requirement and the typical design using BizTalk. After that, I will explain the approach that I have followed. This is about an integration between WorkDay and SAP HR System.

Requirement 

1. Everyday, We receive a file that contains terminated and active employee details from WorkDay System.

2. All the active employees from the file + last X(Example 30) days of terminated employees also should go along. so we have decided to store all terminated employee’s details in BizTalk.

Whenever I receive a file I store them in Local DB, so that it will be fetched and sent along with the original file afterwards. A typical employee records would contain personal, manager details, Employee Status, Additional Information, Assignment details and size of an employee is 7 KB.

Typical Design using Orchestration

  1. Filter and store the terminated employees in DB
  2. Convert WorkDay format active employees into SAP format and call it Active SAP Message Using a Map and keep it alive using orchestration(Map 1)
  3. Get(Map 2) and Convert Terminated Employees from DB into SAP format and call it Termin. SAP Message(Map 3)
  4. Merge Termin. and Active SAP Message into one Single Message and Send it to SAP System.(Map 4)

https://howtologicapp.files.wordpress.com/2017/01/18.png?w=498&h=493&crop=1

https://howtologicapp.files.wordpress.com/2017/01/31.png?w=214&h=493&crop=1

But to improve the performance of the process, we wanted to avoid the communication to LocalDB through MsgBox. Once I remove the flow I have avoided 3 Maps(Maps 1,2 and 3).

Changes in the Orchestration that we designed

  1. Filter and store the terminated employees in DB
  2. Inside single XSLT mapping, Loop through the Active Employees and Make a .net call to retrieve Termin. SQL Records from DB and go through it. Now we have produced a single message that contains both the Active and Termin. Employees.

https://howtologicapp.files.wordpress.com/2017/01/62.png?w=356&h=356&crop=1

https://howtologicapp.files.wordpress.com/2017/01/72.png?w=356&h=356&crop=1

I have removed all the shapes inside the Orchestration and kept only Map which first loops through the records from the file we receive it from WorkDay then from the Local DB using .net SQL Call(which is faster, since it is local db and available all the time we don't need the retry advantage of BizTalk).

Finally, I don't need this orchestration because it just doing one transformation that I could do it in Receive(Inbound Map) or Send Locations(Outbound Map). Thus, the orchestration also has been removed.

I have to stored the whole message of Terminated Employees in DB as XML data type. So that we wouldn’t loose the data and don't need to worry about the datatype. Also we wouldn’t have any changes if the structure changes or additional fields are required in future.

My custom XSLT inside map will look like below,

https://howtologicapp.files.wordpress.com/2017/01/52.png?w=720

This xslt has been configured inside the map that we use to transform the incoming WorkDay message, fetchTeminWorkers_30Days() calls a stored procedure.

https://howtologicapp.files.wordpress.com/2017/01/73.png?w=720

The above C# code has used XPathDocument which is read only XML Document so low memory will be consumed to return XPathNodeIterator. With this code I am able to fetch 1.3 GB of data which is 200 k records.

The stored procedure looks like,

https://howtologicapp.files.wordpress.com/2017/01/83.png?w=720

Using this approach I was able to successfully fetch and process 30 k records(approx. 250 MB of data). So this would be a good approach if you store your data, fetch and generate XML inside the Map. Custom XSL has a better performance over the Map generated XSL, so we could write custom XSL and take advantages of the MAP functoid(for example if you use cross reference functoid). I feel this really gives more strength to Messaging based solutions in BizTalk.

Thank you for your time and let me know your comments.

See Also

Another important place to find a huge amount of BizTalk related articles is the TechNet Wiki itself. The best entry point is BizTalk Server Resources on the TechNet Wiki