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>Cliente 1234</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.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
{ "ns0:TypedPolling": { "@xmlns:ns0": "http://schemas.microsoft.com/Sql/2008/05/TypedPolling/OutboundEDI", "ns0:TypedPollingResultSet0": { "ns0:TypedPollingResultSet0": [ { "ns0:InvoiceId": "{{content.InvoiceId}}", "ns0:InvoiceDate": "{{content.InvoiceDate | Date: "yyyy-MM-ddTHH:mm:ss"}}", "ns0:ClientId": "{{content.ClientId}}", "ns0:ClientName": "{{content.ClientName}}", "ns0:Amount": "{{content.Amount}}", "ns0:StatusProcessing": "{{content.StatusProcessing}}" } ] } } } |
Once I have the JSON of my XML representation, I just have to use the XML function from Logic Apps and call the BizTalk SQL_To_EDI map as below.
Outbound EDI document generation
Cool, now I have a XML of the EDI document that I want and I need to convert it to the proper EDI format. To do this, use the Encode to EDI action specifying which agreement you want to use.
And the last action will be to send the document to the location you want, which in my case was a blob storage.
Integration Account
Since we created another map to handle the Logic App SQL connector JSON to the BizTalk SQL adapter XML as I told you above, we need to add this map to the integration account.
Another thing that I had to do was to configure properly the Send settings of the agreement I’m using to specify that I’ll be handling the INVOIC EDI message.
The workflow state of this Outbound EDI Logic App is as below.
Summary
In this post I went through all the process of migrating a BizTalk solution to send out an EDI document using Logic Apps with an Integration Account.