Formatting Array Variable as HTML inside Fabric Data Pipeline

[prabhatnath]

Hi Friends,

I have an Array variable that stores below content from the Activity output.

How can I format this to a HTML table (using a Set Variable activity or some Dyanmic Content Expression) so that I can send the message using either Teams or Office 365 outlook Activity.

Please suggest or what is the suggested way to send the run result as below?

{

"name": "Combined_Execution_Result",

"value": [

"{\"source_table_name\": \"EHS\", \"target_table_name\": \"FinalValues\", \"rows_inserted\": \"0\", \"rows_updated\": \"44\"}",

"{\"source_table_name\": \"STP\", \"target_table_name\": \"FinalValues\", \"rows_inserted\": \"0\", \"rows_updated\": \"22\"}"

]

}

Thanks,

Prabhat

[hackcrr]

Hi, @prabhatnath 

To format an array variable into an HTML table in Azure Data Factory or Fabric Data Pipeline and send it using Teams or Office 365 Outlook, you need to perform the following steps:
First, you need to convert the array to an HTML table string.
Second, you need to send the HTML table string via Teams or Outlook.

We need to convert the array to an HTML table. We can use an Azure Data Factory Data Flow or pipeline activities to do this.

{
    "name": "Combined_Execution_Result",
    "value": [
        "{\"source_table_name\": \"EHS\", \"target_table_name\": \"FinalValues\", \"rows_inserted\": \"0\", \"rows_updated\": \"44\"}",
        "{\"source_table_name\": \"STP\", \"target_table_name\": \"FinalValues\", \"rows_inserted\": \"0\", \"rows_updated\": \"22\"}"
    ]
}

Add a Set Variable activity to your pipeline. In the Name field, enter the name of your new variable, e.g., FormattedHtmlTable. In the Value field, use the following expression to convert the array to an HTML table:

@concat(
    '<table border="1"><tr><th>Source Table Name</th><th>Target Table Name</th><th>Rows Inserted</th><th>Rows Updated</th></tr>',
    join(
        array(
            forEach(item in activity('YourActivityName').output.value, 
            concat('<tr><td>', item.source_table_name, '</td><td>', item.target_table_name, '</td><td>', item.rows_inserted, '</td><td>', item.rows_updated, '</td></tr>')
            )
        ),
        ''
    ),
    '</table>'
)

Add a Web Activity to your pipeline to send a message to Teams.
In the Settings tab, configure the following:
URL: Your Teams Webhook URL.
Method: POST.
Body: Use the following JSON, replacing <FormattedHtmlTable> with the expression to get the variable's value:

{
    "type": "message",
    "attachments": [
        {
            "contentType": "application/vnd.microsoft.card.adaptive",
            "content": {
                "$schema": "http://adaptivecards.io/schemas/adaptive-card.json",
                "type": "AdaptiveCard",
                "version": "1.2",
                "body": [
                    {
                        "type": "TextBlock",
                        "text": "Pipeline Execution Results",
                        "weight": "Bolder",
                        "size": "Medium"
                    },
                    {
                        "type": "TextBlock",
                        "text": "<FormattedHtmlTable>",
                        "wrap": true
                    }
                ]
            }
        }
    ]
}

Best Regards,

hackcrr

If this post helps, then please consider Accept it as the solution and kudos to this post to help the other members find it more quickly

[prabhatnath]

Thank you hackcrr for your help. 

I am getting below error when I am using the HTML fomatting expression inside the Set Variable activity.

Unrecognized expression: item in activity('CombineResults').output.value

Not able to figureout what could be the issue. Please suggest.

The previous activity was also a Set Variable (CombineResults) which is producing the JSON output to the Combined_Execution_Result variable of Array type. So I think the Array variable Combined_Execution_Result has the JSON.