Migrating an EDI solution from BizTalk to Logic Apps – Part 3

In this 3rd party of the series, I’ll be talking about how to send out EDI documents using Logic Apps. Look at the 1st part of the series, where I explained all the configurations needed in a BizTalk environment. Check also the 2nd part of the series, where I explained how to work with incoming EDI documents.

Logic App Workflow

This process starts on the table OutboundEDI in the database that I have. The Logic App will use the SQL connector to detect when there’s a new record that needs to be processed. Then it will execute the transformation to an EDI document and send it to a storage location

Logic App SQL connector schema vs BizTalk SQL adapter schema

Once it has the record, it will need to transform it from the JSON format received to the SQL format expected by the map created in BizTalk that we copied to the integration account. Below you can see the schema of the XML we need, the JSON we are receiving from the SQL connector and its representation in XML.

1

2

3

4

5

6

7

8

9

10

11

{

"@odata.etag":"",

"ItemInternalId":"969256c7-f27a-4e6c-be48-8c2da56c48ae",

"InvoiceId":6,

"InvoiceDate":"2018-09-21T00:00:00Z",

"ClientId":"C1234",

"ClientName":"Client 1234",

"Amount":2800,

"StatusProcessing":"Pending",

"LastUpdated":"AAAAAAAAF3I="

}

1

2

3

4

5

6

7

8

9

10

<Root odata.etag="">

<ItemInternalId>54af1bad-5eb6-4639-8ad6-ffdf8d694c1d</ItemInternalId>

<InvoiceId>4</InvoiceId>

<InvoiceDate>2018-08-12T00:00:00Z</InvoiceDate>

<ClientId>C1234</ClientId>

<ClientName>Cliente1234</ClientName>

<Amount>2350</Amount>

<StatusProcessing>Pending</StatusProcessing>

<LastUpdated>AAAAAAAAC8M=</LastUpdated>

</Root>

As you can see above, the XML generated by the JSON/XML from the Logic Apps SQL connector is not matching with the map we did in our BizTalk solution below:

To solve this, we have two options:

Change the map we have from the BizTalk solution to accept this new XML;

Create a map to transform from the JSON/XML we have from the Logic App SQL connector to the XML we have from the BizTalk SQL adapter;

Looking at this map that only has 6 fields, we can say that it would be better to just create a new map in BizTalk to handle this new XML right? But in my real-life situation, the schemas were much larger and the maps were much more complex and recreating all of them was going to be just too much work. Also, since I know all these BizTalk maps work fine (after all they are running like this in production), I just need to concentrate on testing the new stuff rather than retesting all BizTalk maps.

So, my decision was to go with the option 2. To do so, I created a liquid template to transform from the Logic App SQL connector JSON to the BizTalk SQL adapter XML.

But there’s no action that transforms from JSON to XML. So, what I had to do was to create a JSON to JSON transformation, where the BizTalk SQL adapter XML was represented by a JSON schema of it. Below it’s the Liquid template of this transformation.

Follow me

I'm an Integration Specialist, Certified Azure, BizTalk, and Mulesoft working with integrations technologies like BizTalk, Logic App, Service Bus, Azure Functions, and Mulesoft.
Currently working as a Lead Integration Consultant for Theta in Auckland, New Zealand.
If you need an integration specialist for designing a solution, mentoring, training, or just someone to talk to understand better your options, contact me.