Parse a web activity error message into a synapse field

Question

I have been trying to log an error from a web activity (POST method) into a field in a synapse table. The problem is, there are some special characters in the message key string like:

{
    "value": [
        {
            "id": "",
            "runId": "",
            "debugRunId": ,
            "runGroupId": "",
            "pipelineName": "my_dynamic_pipeline_name",
            "parameters": {
                "region_code": "",
                "data_start_date": "",
                "data_end_date": "",
                "etl_insert_batch_id": "",
                "pipeline_subject_area": "",
                "type_of_request": "",
                "pipeline_name": "",
                "pipeline_requested_by": "",
                "debug": "",
                "cdmloadtype": ""
            },
            "invokedBy": {
                "id": "",
                "name": "",
                "invokedByType": ""
            },
            "runStart": "",
            "runEnd": "",
            "durationInMs": ,
            "status": "",
            "message": "Operation on target my_dynamic_pipeline_name failed: Operation on target my_dynamic_dataflow_name failed: {\"StatusCode\":\"DFExecutorUserError\",\"Message\":\"Job failed due to reason: at Sink 'SinkutilFailedDummy': java.sql.BatchUpdateException: Execution Status - FAILED ;Error number - 15165 ;Pipeline name - my_dynamic_pipeline_name; Stored procedure name - my_stored_proc_name ; Error step - Step 3: Key Hash and Util Type Hash Generation ; Insert batch ID - 1816 ; Error Message - Could not find object 'my object' or you do not have permission.\",\"Details\":\"java.sql.BatchUpdateException: Execution Status - FAILED ;Error number - 15165 ;Pipeline name - my_dynamic_pipeline_name; Stored procedure name - my_stored_proc_name ; Error step - Step 3: Key Hash and Util Type Hash Generation ; Insert batch ID - 1816 ; Error Message - Could not find object 'my_object' or you do not have permission.\\n\\tat shaded.msdataflow.com.microsoft.sqlserver.jdbc.SQLServerStatement.executeBatch(SQLServerStatement.java:1845)\\n\\tat com.microsoft.dataflow.transformers.store.JDBCWriter.executeBatchSQLs(JDBCStore.scala:462)\\n\\tat com.microsoft.dataflow.transformers.store.JDBCWriter.executeSQL(JDBCStore.scala:440)\\n\\tat com.microsoft.dataflow.transformers.store.JDBCWriter$$anonfun$executeTableOpAndPostSQLAndDDL$2.apply$mcV$sp(JDBCStore.scala:494)\\n\\tat com.microsoft.dataflow.transformers.store.JDBCWriter$$anonfun$executeTableOpAndPostSQLAndDDL$2.apply(JDBCStore.scala:494)\\n\\tat com.microsoft.dataflow.transformers.store.JDBCWriter$$anonfun$executeTableOpAndPostS\"}",
...
}

So I can filter down the output with:

@activity('pingPL').output.value[0].message

but there are {} and $ special characters that the Data Flow expression is trying to evaluate. I already try to use replace or string functions in the pipeline expression or in the dataflow expression without success.

Is there a way to parse this as a string or get to the Message key?, something like:

@activity('pingPL').output.value[0].message*.failed*.failed.Message

Update:

This seems to be working:

@json(split(activity('pingPL').output.value[0].message, 'failed: ')[2]).Message

I can split by failed: and the index 2 will give me the error logs within the {...}. I can parse that as a json and use the Message key. It is working but it is not the ideal dynamic solution since the error message wouldn't have always the same structure.

Answer 1

Got a solution using substring and indexof to extract the {...} info:

substring(activity('pingPL').output.value[0].message,indexof(activity('pingPL').output.value[0].message,'{'),sub(indexof(activity('pingPL').output.value[0].message,'}'),sub(indexof(activity('pingPL').output.value[0].message,'{'),1)))

Getting this string as the output:

{\"StatusCode\":\"DFExecutorUserError\",\"Message\":\"Job failed due to reason: at Sink 'SinkutilFailedDummy': java.sql.BatchUpdateException: Execution Status - FAILED ;Error number - 15165 ;Pipeline name - my_dynamic_pipeline_name; Stored procedure name - my_stored_proc_name ; Error step - Step 3: Key Hash and Util Type Hash Generation ; Insert batch ID - 1816 ; Error Message - Could not find object 'my object' or you do not have permission.\",\"Details\":\"java.sql.BatchUpdateException: Execution Status - FAILED ;Error number - 15165 ;Pipeline name - my_dynamic_pipeline_name; Stored procedure name - my_stored_proc_name ; Error step - Step 3: Key Hash and Util Type Hash Generation ; Insert batch ID - 1816 ; Error Message - Could not find object 'my_object' or you do not have permission.\\n\\tat shaded.msdataflow.com.microsoft.sqlserver.jdbc.SQLServerStatement.executeBatch(SQLServerStatement.java:1845)\\n\\tat com.microsoft.dataflow.transformers.store.JDBCWriter.executeBatchSQLs(JDBCStore.scala:462)\\n\\tat com.microsoft.dataflow.transformers.store.JDBCWriter.executeSQL(JDBCStore.scala:440)\\n\\tat com.microsoft.dataflow.transformers.store.JDBCWriter$$anonfun$executeTableOpAndPostSQLAndDDL$2.apply$mcV$sp(JDBCStore.scala:494)\\n\\tat com.microsoft.dataflow.transformers.store.JDBCWriter$$anonfun$executeTableOpAndPostSQLAndDDL$2.apply(JDBCStore.scala:494)\\n\\tat com.microsoft.dataflow.transformers.store.JDBCWriter$$anonfun$executeTableOpAndPostS\"}

Then I used json expression to extract the key message:

json('extracted string').message

Then use replace to remove the single quotations ' to avoid a sql error.

This is the final expression I got to extract the error message:

@replace(json(substring(activity('pingPL').output.value[0].message,indexof(activity('pingPL').output.value[0].message,'{'),sub(indexof(activity('pingPL').output.value[0].message,'}'),sub(indexof(activity('pingPL').output.value[0].message,'{'),1)))).message,'''','-')