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
Flat file handling within Logic Apps standard itself
Logic Apps standard — XML transformation issue
Logic App, XML Transformation issue, Special characters, Disable the byte order mark
- 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).
- 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.
- 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.
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
Extension for Visual Studio - Add-in for the Integration Account project to support XML Schema creation, XML transform…
If you have visual studio 2015 only available, the same add-in is available
Logic Apps Enterprise Integration Tools
Azure App Service is a fully managed PaaS (Platform as a Service) for developers that makes it easier to build web…
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
I am able to include my flat-file schemas in my standard logic app but the flat file connector is not available. I…
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.
- Use Flat file decoder with the source file XSD to convert the delimited flat file to xml
- Use transformation shape with the XSLT to convert the source file to destination xml file
- Use flat file encoder with the destination file XSD to generate the equivalent flat file
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">
<claim name="sub" match="any">