Logic App Standard — Working with Flat Files

In integration world, you can’t avoid working with flat files. One way or the other a delimited/positional flat file will be generated by a system that will need processing, either transforming it to a xml or json or another type of flat file. I have come across such scenario recently where my source system generated a delimited flat file and my destination system needed the same file transformed to a positional flat file.

Update: Flat file capabilities are introduced in Logic App standard now. Refer this article to know more about it:

Logic App Standard — Working with Flat Files — Part II

Logic Apps standard — XML transformation issue

Tools needed:

1. For the delimited flat file, a sample file is provided but no schema available. Same case with the positional flat file. So i needed a tool to model a flat file schema just by using a sample instance file (XSD).
2. Once we get the schemas ready, a handy tool to perform the mapping/transformation. Like in the olden days BizTalk mapper or MuleSoft DataWeaver (XSLT) or any other services.
3. Once the schemas and maps are ready, need some utilities to be able to perform flat file decoding, encoding using the schemas and a transformation using the map.

Tools used:

The client integration team were adopting Azure Integration Services. Logic Apps standard is strategically chosen to build integration solutions.

Build Schemas and maps: If you are a Microsoft developer, the following options are available.

If you have a BizTalk development VM lying around, thats a perfect place to build the schemas and maps.

If you are using Visual Studio 2019, the following add-in can be used to build schemas and maps.

Azure Logic Apps Enterprise Integration Tools - Visual Studio Marketplace

If you have visual studio 2015 only available, the same add-in is available

Logic Apps Enterprise Integration Tools

Now with these tools, flat file schemas can be built with the available sample files. With the positional flat files, there were requirements to allow early terminations, optional fields, nillable fields. Lots of fun playing with flat file schema. Brought back the BizTalk day memories.

Once the schemas are ready use the mapper to create the transformation between delimited flat file to positional flat file. Generate the XSLT by doing validate map functionality. Schemas and map are ready.

Logic app consumption supports reading XSD schemas and XSLT maps from the Integration Accounts. Logic Apps standards has schemas and maps built in so naturally the idea was to build schemas and maps, store there and use to achieve the result. Surprise surprise.

Logic App standard does not have flat file encoder and decoder. There is an issue with Microsoft Logic App team and in the backlog. You can track the status here:

Flat File decoding not available in standard logic apps · Issue #371 · Azure/logicapps

It will be few month before the flat file features available at Logic App standard. What do we do in the interim? How do we resolve this issue for now?

Build the flat file decoding and encoding, transformation using Logic Apps consumption and integration account.

1. Use Flat file decoder with the source file XSD to convert the delimited flat file to xml
2. Use transformation shape with the XSLT to convert the source file to destination xml file
3. Use flat file encoder with the destination file XSD to generate the equivalent flat file

Easy peasy!

But there comes the Security: How do we secure this flat file transformation solution?

Expose the logic apps consumption solution using the API Management; remove the SAS token section from the endpoint so it doesn’t have multiple authentication scheme enabled

Exposing the logic app endpoint using API management is one of the best ways of securing the endpoint. Most of the organisation will have necessary security controls built around API management to protect them from security attacks. To give some examples, enabling internal mode using a vnet, exposing API management via Application Gateway or Front door and the usage of endpoint protection policies.

IP whitelist the logic apps trigger to accept only API Managements public IP

Enable Authorisation header at logic app consumption solution, this will enable the logic app to receive an authorisation header with a bearer token

Enable validate-jwt policy at the API management proxy layer with the right subject and audience.

<validate-jwt header-name="Authorization" failed-validation-httpcode="401" failed-validation-error-message="Unauthorized. Access token is missing or invalid.">
  <openid-config url="https://login.microsoftonline.com/<tenantID>/.well-known/openid-configuration" />
  <claim name="aud" match="any">
       <value><audienceID></value>
  </claim>
  <claim name="sub" match="any">
       <value><subjectID></value>
  </claim>
</validate-jwt>