Search code examples
azureazure-logic-appsazure-data-factoryazure-logic-app-standard

How to grab column value from ADLS gen 2 csv file and use the column value in the body of the email,also send blob data as attachment to outlook mail

Here is my Scenario, There will be a drop of csv file into blob storage every day ,that will be processed by my dataflow in ADF and generate a csv in output folder.

Now Using logic apps, I need to send that csv file (less than 10 mb ) as an attachement to customer via Outlook connector. Besides ,My body of the email must have dynamic value coming from that blob csv . For example 'AppWorks' is the column value in column 'Works/not'. Sometimes it may be "AppNotWorks".So How to handle this scenario in Azure logic apps

Solution

  • You can use the combination of both data factory and logic apps to do this. Use look up activity to get the first row of the file (Since the entire column value will be same, we can get the required value from one row).

    enter image description here

    • Now use web activity to trigger the logic app. Pass the logic app's HTTP request URL to web activity. In the body, pass the following dynamic content:
    @activity('Lookup1').output.firstRow

    enter image description here

    • When you debug the pipeline, the logic app will be successfully triggered. I have given the Request Body JSON schema to get values individually. For the sample I have taken, it would look as shown below:
    {
    "properties": {
        "customer": {
            "type": "string"
        },
        "id": {
            "type": "string"
        }
    },
    "type": "object"
}
    • Create a connection to storage account to link the required file.

    enter image description here

    • Now, using the Outlook connector, send the Email.

    enter image description here

    • The following is the entire Logic app JSON:
    {
    "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('JTJmZGF0YSUyZnNhbXBsZTEuY3N2'))}/content",
                    "queries": {
                        "inferContentType": true
                    }
                },
                "metadata": {
                    "JTJmZGF0YSUyZnNhbXBsZTEuY3N2": "/data/sample1.csv"
                },
                "runAfter": {},
                "type": "ApiConnection"
            },
            "Send_an_email_(V2)": {
                "inputs": {
                    "body": {
                        "Attachments": [
                            {
                                "ContentBytes": "@{base64(body('Get_blob_content_(V2)'))}",
                                "Name": "sample1.csv"
                            }
                        ],
                        "Body": "<p>Hi @{triggerBody()?['customer']},<br>\n<br>\nRandom description</p>",
                        "Importance": "Normal",
                        "Subject": "sample data",
                        "To": "<to_email>"
                    },
                    "host": {
                        "connection": {
                            "name": "@parameters('$connections')['office365']['connectionId']"
                        }
                    },
                    "method": "post",
                    "path": "/v2/Mail"
                },
                "runAfter": {
                    "Get_blob_content_(V2)": [
                        "Succeeded"
                    ]
                },
                "type": "ApiConnection"
            }
        },
        "contentVersion": "1.0.0.0",
        "outputs": {},
        "parameters": {
            "$connections": {
                "defaultValue": {},
                "type": "Object"
            }
        },
        "triggers": {
            "manual": {
                "inputs": {
                    "schema": {
                        "properties": {
                            "customer": {
                                "type": "string"
                            },
                            "id": {
                                "type": "string"
                            }
                        },
                        "type": "object"
                    }
                },
                "kind": "Http",
                "type": "Request"
            }
        }
    },
    "parameters": {
        "$connections": {
            "value": {
                "azureblob": {
                    "connectionId": "/subscriptions/xxx/resourceGroups/xxx/providers/Microsoft.Web/connections/azureblob",
                    "connectionName": "azureblob",
                    "id": "/subscriptions/xxx/providers/Microsoft.Web/locations/westus2/managedApis/azureblob"
                },
                "office365": {
                    "connectionId": "/subscriptions/xxx/resourceGroups/v-sarikontha-Mindtree/providers/Microsoft.Web/connections/office365",
                    "connectionName": "office365",
                    "id": "/subscriptions/xxx/providers/Microsoft.Web/locations/westus2/managedApis/office365"
                }
            }
        }
    }
}
    • The following is the resulting Mail image for reference:

    enter image description here