Search code examples
azureazure-logic-apps

Migrate data from azure data lake into azure sql using azure logicapps

enter image description here

I need to create a logic app and my requirement is ,I need to migrate A json file from azure data lake Gen2 folder and based on some value need to convert that json into xml and then send it to SQL.

So based on my requirement i tried the workflow as shown in image but i am not sure whether my approach is wrong or right.

could you please provide in detail azure logic app workflow structure for my requirement by providing in detail trigger and actions images and expression.

Any early and detail response would be appreciated.

Solution

  • I have reproduced in my environment and below are expected results :

    Input:

    {
"UserID":88888,
"FirstName":"Rithwik",
"LastName":"Bojja",
"Email":"[email protected]"
}

    enter image description here

    File in Azure Storage Account:

    enter image description here

    Design:

    enter image description here

    Then:

    enter image description here

    Output:

    enter image description here

    Code view for replication:

    {
    "definition": {
        "$schema": "https://schema.management.azure.com/providers/Microsoft.Logic/schemas/2016-06-01/workflowdefinition.json#",
        "actions": {
            "Get_blob_content_(V2)": {
                "inputs": {
                    "host": {
                        "connection": {
                            "name": "@parameters('$connections')['azureblob']['connectionId']"
                        }
                    },
                    "method": "get",
                    "path": "/v2/datasets/@{encodeURIComponent(encodeURIComponent('AccountNameFromSettings'))}/files/@{encodeURIComponent(encodeURIComponent('JTJmcml0aHdpayUyZnRlc3R0Lmpzb24='))}/content",
                    "queries": {
                        "inferContentType": true
                    }
                },
                "metadata": {
                    "JTJmcml0aHdpayUyZnRlc3R0Lmpzb24=": "/rithwik/testt.json"
                },
                "runAfter": {},
                "type": "ApiConnection"
            },
            "Insert_row_(V2)": {
                "inputs": {
                    "body": {
                        "Email": "@body('Parse_JSON')?['Email']",
                        "FirstName": "@body('Parse_JSON')?['FirstName']",
                        "LastName": "@body('Parse_JSON')?['LastName']",
                        "UserID": "@body('Parse_JSON')?['UserID']"
                    },
                    "host": {
                        "connection": {
                            "name": "@parameters('$connections')['sql']['connectionId']"
                        }
                    },
                    "method": "post",
                    "path": "/v2/datasets/@{encodeURIComponent(encodeURIComponent('default'))},@{encodeURIComponent(encodeURIComponent('default'))}/tables/@{encodeURIComponent(encodeURIComponent('[dbo].[Users]'))}/items"
                },
                "runAfter": {
                    "Parse_JSON": [
                        "Succeeded"
                    ]
                },
                "type": "ApiConnection"
            },
            "Parse_JSON": {
                "inputs": {
                    "content": "@json( body('Get_blob_content_(V2)'))",
                    "schema": {
                        "properties": {
                            "Email": {
                                "type": "string"
                            },
                            "FirstName": {
                                "type": "string"
                            },
                            "LastName": {
                                "type": "string"
                            },
                            "UserID": {
                                "type": "integer"
                            }
                        },
                        "type": "object"
                    }
                },
                "runAfter": {
                    "Get_blob_content_(V2)": [
                        "Succeeded"
                    ]
                },
                "type": "ParseJson"
            }
        },
        "contentVersion": "1.0.0.0",
        "outputs": {},
        "parameters": {
            "$connections": {
                "defaultValue": {},
                "type": "Object"
            }
        },
        "triggers": {
            "manual": {
                "inputs": {
                    "schema": {}
                },
                "kind": "Http",
                "type": "Request"
            }
        }
    },
    "parameters": {
        "$connections": {
            "value": {
                "azureblob": {
                    "connectionId": "/subscriptions/b83c1ed3/resourceGroups/rbojja/providers/Microsoft.Web/connections/azureblob",
                    "connectionName": "azureblob",
                    "id": "/subscriptions/b83c1ed3/providers/Microsoft.Web/locations/eastus/managedApis/azureblob"
                },
                "sql": {
                    "connectionId": "/subscriptions/b83c1ed3/resourceGroups/rbojja/providers/Microsoft.Web/connections/sql",
                    "connectionName": "sql",
                    "id": "/subscriptions/b83c1ed3/providers/Microsoft.Web/locations/eastus/managedApis/sql"
                }
            }
        }
    }
}