How to pass array of values to logic app?

Question

There is a requirement to send an alert mail from ADF that looks like below.

Input: Input are files from FTP. We have 4 files, say 1,2,3,4, and there may be scenarios where one or more of them may be missing. So, I have to loop through the files and sent an alert mail with the details like that of the image shared above.

The column 'File Availability in FTP' will be set to 'Available' if the files exists for that particular day, otherwise, 'Unavailable'. I have to use a logic app to send notification mail. If I have to use a for each and append the files to variable, and pass that variable to logic app, what should the comparison condition be?

Can somebody help me with the solution?

How does your input look? Can you provide basic input you have — RithwikBojja, Jul 25 '23 at 09:38
Input are files from FTP. We have 4 files, say 1,2,3,4, and there may be scenarios where one or more of them may be missing. So, I have to loop through the files and sent an alert mail with the details like that of the image shared above. @RithwikBojja — Sivaani N, Jul 25 '23 at 09:50

Rakesh Govindula · Accepted Answer · 2023-07-25T15:49:19.480

You can follow the below approach using the Get Meta data activity. Get Meta data activity supports FTP, so use this to check whether the file exists or not.

Here, my files are from blob storage, for you it will be FTP dataset.

First create an empty array variable for storing the file name and availability status.

Now, pass your another array which contains your file names to a Foreach activity. Inside ForEach, take the Get Meta data activity with dataset parameter for the file name and give the @item() to the file name parameter. Use the exists of Get Meta data activity like below.

enter image description here

Now, give this expression @activity('Get Metadata1').output.exists in an if activity. This will give true or false about the file availability.

Inside the True activities of if, use an append variable activity. Use the below expression to append the value to the empty array we create earlier.

@json(concat('{','"filename":"',item(),'","File Availability in FTP":"Available"}'))

And do the same in the False activities of if with below expression.

@json(concat('{','"filename":"',item(),'","File Availability in FTP":"Unvailable"}'))

This will give the array like below.

enter image description here

Now, we need to pass this array to the logic app. Use Web activity with post method to call the logic app and pass this in the body of it.

enter image description here

This is my ADF pipeline JSON:

{
    "name": "pipeline2",
    "properties": {
        "activities": [
            {
                "name": "ForEach1",
                "type": "ForEach",
                "dependsOn": [],
                "userProperties": [],
                "typeProperties": {
                    "items": {
                        "value": "@variables('file_names_arr')",
                        "type": "Expression"
                    },
                    "isSequential": true,
                    "activities": [
                        {
                            "name": "Get Metadata1",
                            "type": "GetMetadata",
                            "dependsOn": [],
                            "policy": {
                                "timeout": "0.12:00:00",
                                "retry": 0,
                                "retryIntervalInSeconds": 30,
                                "secureOutput": false,
                                "secureInput": false
                            },
                            "userProperties": [],
                            "typeProperties": {
                                "dataset": {
                                    "referenceName": "sourcecsv",
                                    "type": "DatasetReference",
                                    "parameters": {
                                        "filename": {
                                            "value": "@item()",
                                            "type": "Expression"
                                        }
                                    }
                                },
                                "fieldList": [
                                    "exists"
                                ],
                                "storeSettings": {
                                    "type": "AzureBlobFSReadSettings",
                                    "enablePartitionDiscovery": false
                                },
                                "formatSettings": {
                                    "type": "DelimitedTextReadSettings"
                                }
                            }
                        },
                        {
                            "name": "If Condition1",
                            "type": "IfCondition",
                            "dependsOn": [
                                {
                                    "activity": "Get Metadata1",
                                    "dependencyConditions": [
                                        "Succeeded"
                                    ]
                                }
                            ],
                            "userProperties": [],
                            "typeProperties": {
                                "expression": {
                                    "value": "@activity('Get Metadata1').output.exists",
                                    "type": "Expression"
                                },
                                "ifFalseActivities": [
                                    {
                                        "name": "Append variable2",
                                        "type": "AppendVariable",
                                        "dependsOn": [],
                                        "userProperties": [],
                                        "typeProperties": {
                                            "variableName": "res_arr",
                                            "value": {
                                                "value": "@json(concat('{','\"filename\":\"',item(),'\",\"File Availability in FTP\":\"Unvailable\"}'))",
                                                "type": "Expression"
                                            }
                                        }
                                    }
                                ],
                                "ifTrueActivities": [
                                    {
                                        "name": "Append variable1",
                                        "type": "AppendVariable",
                                        "dependsOn": [],
                                        "userProperties": [],
                                        "typeProperties": {
                                            "variableName": "res_arr",
                                            "value": {
                                                "value": "@json(concat('{','\"filename\":\"',item(),'\",\"File Availability in FTP\":\"Available\"}'))",
                                                "type": "Expression"
                                            }
                                        }
                                    }
                                ]
                            }
                        }
                    ]
                }
            },
            {
                "name": "Set variable1",
                "type": "SetVariable",
                "dependsOn": [
                    {
                        "activity": "ForEach1",
                        "dependencyConditions": [
                            "Succeeded"
                        ]
                    }
                ],
                "policy": {
                    "secureOutput": false,
                    "secureInput": false
                },
                "userProperties": [],
                "typeProperties": {
                    "variableName": "dup",
                    "value": {
                        "value": "@variables('res_arr')",
                        "type": "Expression"
                    }
                }
            },
            {
                "name": "Web1",
                "type": "WebActivity",
                "dependsOn": [
                    {
                        "activity": "Set variable1",
                        "dependencyConditions": [
                            "Succeeded"
                        ]
                    }
                ],
                "policy": {
                    "timeout": "0.12:00:00",
                    "retry": 0,
                    "retryIntervalInSeconds": 30,
                    "secureOutput": false,
                    "secureInput": false
                },
                "userProperties": [],
                "typeProperties": {
                    "url": "<HTTP POST URL of 'When a HTTP request is received' trigger>",
                    "method": "POST",
                    "body": {
                        "value": "@variables('res_arr')",
                        "type": "Expression"
                    }
                }
            }
        ],
        "variables": {
            "file_names_arr": {
                "type": "Array",
                "defaultValue": [
                    "Sample1.csv",
                    "Sample2.csv",
                    "Sample3.csv",
                    "Sample4.csv"
                ]
            },
            "res_arr": {
                "type": "Array"
            },
            "dup": {
                "type": "Array"
            }
        },
        "annotations": []
    }
}

In the logic app, Use the Create HTML table, to create the table from this array and pass the output of it to the mail like below.

enter image description here

Run the pipeline and you will get the desired result like below.

enter image description here

Update:

Pipeline JSON to add Serial Number:

{
    "name": "pipeline2",
    "properties": {
        "activities": [
            {
                "name": "ForEach1",
                "type": "ForEach",
                "dependsOn": [
                    {
                        "activity": "Intialize counter to 1",
                        "dependencyConditions": [
                            "Succeeded"
                        ]
                    }
                ],
                "userProperties": [],
                "typeProperties": {
                    "items": {
                        "value": "@variables('file_names_arr')",
                        "type": "Expression"
                    },
                    "isSequential": true,
                    "activities": [
                        {
                            "name": "Get Metadata1",
                            "type": "GetMetadata",
                            "dependsOn": [],
                            "policy": {
                                "timeout": "0.12:00:00",
                                "retry": 0,
                                "retryIntervalInSeconds": 30,
                                "secureOutput": false,
                                "secureInput": false
                            },
                            "userProperties": [],
                            "typeProperties": {
                                "dataset": {
                                    "referenceName": "sourcecsv",
                                    "type": "DatasetReference",
                                    "parameters": {
                                        "filename": {
                                            "value": "@item()",
                                            "type": "Expression"
                                        }
                                    }
                                },
                                "fieldList": [
                                    "exists"
                                ],
                                "storeSettings": {
                                    "type": "AzureBlobFSReadSettings",
                                    "recursive": true,
                                    "enablePartitionDiscovery": false
                                },
                                "formatSettings": {
                                    "type": "DelimitedTextReadSettings"
                                }
                            }
                        },
                        {
                            "name": "If Condition1",
                            "type": "IfCondition",
                            "dependsOn": [
                                {
                                    "activity": "Get Metadata1",
                                    "dependencyConditions": [
                                        "Succeeded"
                                    ]
                                }
                            ],
                            "userProperties": [],
                            "typeProperties": {
                                "expression": {
                                    "value": "@activity('Get Metadata1').output.exists",
                                    "type": "Expression"
                                },
                                "ifFalseActivities": [
                                    {
                                        "name": "Append variable2",
                                        "type": "AppendVariable",
                                        "dependsOn": [],
                                        "userProperties": [],
                                        "typeProperties": {
                                            "variableName": "res_arr",
                                            "value": {
                                                "value": "@json(concat('{','\"S.NO\":',variables('counter'),',\"filename\":\"',item(),'\",\"File Availability in FTP\":\"Unavailable\"}'))",
                                                "type": "Expression"
                                            }
                                        }
                                    }
                                ],
                                "ifTrueActivities": [
                                    {
                                        "name": "Append variable1",
                                        "type": "AppendVariable",
                                        "dependsOn": [],
                                        "userProperties": [],
                                        "typeProperties": {
                                            "variableName": "res_arr",
                                            "value": {
                                                "value": "@json(concat('{','\"S.NO\":',variables('counter'),',\"filename\":\"',item(),'\",\"File Availability in FTP\":\"Available\"}'))",
                                                "type": "Expression"
                                            }
                                        }
                                    }
                                ]
                            }
                        },
                        {
                            "name": "increment and store in temp",
                            "type": "SetVariable",
                            "dependsOn": [
                                {
                                    "activity": "If Condition1",
                                    "dependencyConditions": [
                                        "Succeeded"
                                    ]
                                }
                            ],
                            "policy": {
                                "secureOutput": false,
                                "secureInput": false
                            },
                            "userProperties": [],
                            "typeProperties": {
                                "variableName": "temp",
                                "value": {
                                    "value": "@string(add(int(variables('counter')),1))",
                                    "type": "Expression"
                                }
                            }
                        },
                        {
                            "name": "store temp to counter",
                            "type": "SetVariable",
                            "dependsOn": [
                                {
                                    "activity": "increment and store in temp",
                                    "dependencyConditions": [
                                        "Succeeded"
                                    ]
                                }
                            ],
                            "policy": {
                                "secureOutput": false,
                                "secureInput": false
                            },
                            "userProperties": [],
                            "typeProperties": {
                                "variableName": "counter",
                                "value": {
                                    "value": "@variables('temp')",
                                    "type": "Expression"
                                }
                            }
                        }
                    ]
                }
            },
            {
                "name": "Set variable1",
                "type": "SetVariable",
                "dependsOn": [
                    {
                        "activity": "ForEach1",
                        "dependencyConditions": [
                            "Succeeded"
                        ]
                    }
                ],
                "policy": {
                    "secureOutput": false,
                    "secureInput": false
                },
                "userProperties": [],
                "typeProperties": {
                    "variableName": "dup",
                    "value": {
                        "value": "@variables('res_arr')",
                        "type": "Expression"
                    }
                }
            },
            {
                "name": "Web1",
                "type": "WebActivity",
                "dependsOn": [
                    {
                        "activity": "Set variable1",
                        "dependencyConditions": [
                            "Succeeded"
                        ]
                    }
                ],
                "policy": {
                    "timeout": "0.12:00:00",
                    "retry": 0,
                    "retryIntervalInSeconds": 30,
                    "secureOutput": false,
                    "secureInput": false
                },
                "userProperties": [],
                "typeProperties": {
                    "url": "<HTTP POST URL of 'When a HTTP request is received' trigger>",
                    "method": "POST",
                    "body": {
                        "value": "@variables('res_arr')",
                        "type": "Expression"
                    }
                }
            },
            {
                "name": "Intialize counter to 1",
                "type": "SetVariable",
                "dependsOn": [],
                "policy": {
                    "secureOutput": false,
                    "secureInput": false
                },
                "userProperties": [],
                "typeProperties": {
                    "variableName": "counter",
                    "value": "1"
                }
            }
        ],
        "variables": {
            "file_names_arr": {
                "type": "Array",
                "defaultValue": [
                    "Sample1.csv",
                    "Sample2.csv",
                    "Sample3.csv",
                    "Sample4.csv"
                ]
            },
            "res_arr": {
                "type": "Array"
            },
            "dup": {
                "type": "Array"
            },
            "counter": {
                "type": "String"
            },
            "temp": {
                "type": "String"
            }
        },
        "annotations": [],
        "lastPublishTime": "2023-07-25T11:32:47Z"
    },
    "type": "Microsoft.DataFactory/factories/pipelines"
}

You need to use dataset parameters for the FTP file name. First create a dataset parameter like this https://i.imgur.com/YqSc0pB.png and give that in the file name of the FTP dataset like this https://i.imgur.com/xc8ypuz.png. I showed it for Blob dataset and it will same for FTP dataset as well. — Rakesh Govindula, Jul 25 '23 at 13:51
Then it will ask to provide the value for that parameter in the Get Meta data activity inside Foreach. — Rakesh Govindula, Jul 25 '23 at 13:52
is there any way to add SNo from logic app or should it be done in adf only? — Sivaani N, Jul 25 '23 at 15:13
You can add both in ADF or logic app. Check my update section pipeline JSON to do it in ADF. — Rakesh Govindula, Jul 25 '23 at 15:50
Sometimes, the pipeline fails by throwing the error. "Field 'exists' failed with error: 'Type=System.Net.WebException,Message=System error.,Source=System,''Type=System.Net.InternalException,Message=System error.,Source=System,'.". Also, SNo 1 is repeated twice. — Sivaani N, Jul 26 '23 at 01:24
It is giving correct S.NO. for me. https://i.imgur.com/MbzvMbL.png Make sure you have all the activities inside the loop as per the updated JSON I have provided. — Rakesh Govindula, Jul 26 '23 at 03:34

How to pass array of values to logic app?

1 Answers1