Azure data factory Dynamic Content

Question

I have the following output from a web activity .

{
    "value": [
        {
            "id": "/subscriptions/xy_csv",
            "name": "xy_csv",
            "type": "Microsoft.code",
            "etag": "6200",
            "properties": {
                "folder": {
                    "name": "samplecodes"
                },
                "content": {
                    "query": "select * from table 1",
                    "metadata": {
                        "language": "sql"
                    },
                    "currentConnection": {
                        "databaseName": "demo",
                        "poolName": "Built-in"
                    },
                    "resultLimit": 5000
                },
                "type": "SqlQuery"
            }
        },
        {
            "id": "/subscriptions/ab_csv",
            "name": "ab_csv",
            "type": "Microsoft.code",
            "etag": "6200",
            "properties": {
                "folder": {
                    "name": "livecode"
                },
                "content": {
                    "query": "select * from table 2",
                    "metadata": {
                        "language": "sql"
                    },
                    "currentConnection": {
                        "databaseName": "demo",
                        "poolName": "Built-in"
                    },
                    "resultLimit": 5000
                },
                "type": "SqlQuery"
            }
        }
]

I would like to create filter activity after the web activity just to filter out items that are saved under the folder name "livecode".

On the filter activity item field I have -@activity('Web1').output.value

On the condition field I have -- @startswith(item().properties.folder.name,'livecode')

The web activity is successful but the filter activity is failed with this error.

{
    "errorCode": "InvalidTemplate",
    "message": "The execution of template action 'FilterFilter1' failed: The evaluation of 'query' action 'where' expression '@startswith(item().properties.folder.name,'sql')' failed: 'The expression 'startswith(item().properties.folder.name,'sql')' cannot be evaluated because property 'folder' doesn't exist, available properties are 'content, type'.",
    "failureType": "UserError",
    "target": "Filter1",
    "details": ""
}

it feels like I am going wrong on how i have written the Condition Dynamic Content filter to navigate to properties.folder.name. I am not sure what is missing in my condition. Can anyone help? thanks Much appreciated.

score 1 · Accepted Answer · answered Dec 15 '22 at 07:14

The error is because of the web activity output's properties object might not contain the folder sometimes.

I have taken the following json and got the same error:

{
   "value":[
      {
         "id":"/subscriptions/xy_csv",
         "name":"xy_csv",
         "type":"Microsoft.code",
         "etag":"6200",
         "properties":{
            "content":{
               "query":"select * from table 1",
               "metadata":{
                  "language":"sql"
               },
               "currentConnection":{
                  "databaseName":"demo",
                  "poolName":"Built-in"
               },
               "resultLimit":5000
            },
            "type":"SqlQuery"
         }
      },
      {
         "id":"/subscriptions/ab_csv",
         "name":"ab_csv",
         "type":"Microsoft.code",
         "etag":"6200",
         "properties":{
            "folder":{
               "name":"livecode"
            },
            "content":{
               "query":"select * from table 2",
               "metadata":{
                  "language":"sql"
               },
               "currentConnection":{
                  "databaseName":"demo",
                  "poolName":"Built-in"
               },
               "resultLimit":5000
            },
            "type":"SqlQuery"
         }
      }
   ]
}

enter image description here

So, you have to modify the filter condition to check whether it contains a folder key or not using the following dynamic content. I have taken your web activity output as a parameter value and took out folder key from properties object:

@startswith(if(contains(item().properties,'folder'),item().properties.folder.name,''),'livecode')

enter image description here

When I debug the pipeline, we get desired result:

enter image description here

Azure data factory Dynamic Content

1 Answers1