Dynamic Flat File Generation Solution using Logic-App in Azure

I recently implemented a flat file export solution using BizTalk for a customer. The purpose of the solution was to develop a generic file export solution which can generate flat files entirely based on database configuration. This kind of implementation requires dynamic resolution of Schema, Map etc. at run-time. Anyone, who has worked on Microsoft BizTalk Server, knows how to dynamically resolve map for transformation or dynamically resolve schema for flat file encoding.

I wanted to achieve the similar solution (though simple version) using Logic App on Microsoft Azure. Below diagram shows a sample scenario for logic-app implementation,

scenario

  1. Product details are stored in AdventureWorks Database.
  2. Latest product details are to be sent to each customer on regular interval (e.g. Daily).
  3. Product details are to be sent in flat file format.
  4. Each customer needs product file in different format.

It took me a day to implement this solution in Logic App and the designer looks like below,

LogicAppFlow

Logic App Steps:

    1. Recurrence: Triggers process once every day.
    2. Get Product Export File List: Executes a stored procedure to fetch list of product file to be generated. This returns following columns,
      1. Customer ID
      2. ProductExportSchema – holds Customer specific flat file schema name
      3. ProductCanonical_TO_CustomerFile  – holds map name which converts canonical xml to customer specific products file xml.
        Schema and maps with exact same name will be uploaded to Azure Integration Account on Azure.
        usp_GetProductFileExportLIst
        Schemasmaps
    3. For Each Customer: Executes file generation process for each row from previous step.
 "foreach": "@body('Get_Product_Export_File_List')?['ResultSets']['Table1']"
  1. Get Products Data: Executes SP to fetch product data for each customer. In this demo this SP is same for all customer but you can make it dynamic too.
  2. Transform XML: Apply map to transform Products data XML (canonical schema) into customer specific XML (customer’s flat file schema).

    Note
    : Product Data is received from previous step by executing stored procedure. The challenge is that SP response will be in below JSON format but map works on XML message.
    ProductsJsonConvert JSON to XML : Logic app comes with inbuilt function which can transform JSON to XML. It took me a while to come up with below expression (see “content”:) to get the desired canonical XML.

    Also see how map name value is provided from item object of for-each-loop.

    Logic App code view,

    "Transform_XML": {
        "inputs": {
        "content": "@xml(json(concat('{\"Products\":{\"@xmlns\":\"https://pgcan/products/canonical\",\"Product\":',body('Execute_stored_procedure').ResultSets.Table1,'}}')))",
        "integrationAccount": {
          "map": {
             "name": "@{item()?['ProductCanonical_TO_CustomerFile']}"
                 }
        }
    }
  3. Flat File Encoding: This action requires schema name as one of its parameter. I assigned schema name from the item object of for-each-loop
    FlatFileEncoding.PNG
    Code View:

    "Flat_File_Encoding": {
        "inputs": {
            "content": "@{body('Transform_XML')}",
            "integrationAccount": {
            "schema": {
                "name": "@{item()?['ProductExportSchema']}"
             }
         }
    }
    
  4. Create Blob: This is for simplicity. However in real world file can be uploaded to SFTP location.

The solution works fine and it generates Product data file based on entry in LogicAppParamStore table in my database,
LogicAppParamStore

Conclusion:

Efforts: I realized that LogicApp has grown so rich that in above case implementation was even more simple in logic app then BizTalk.
Cost: Logic App would turned out to be almost negligible against the cost involved in setting up BizTalk environment for above solution.