Azure Data Factory- How to feed data into a foreach activity from setvariable activity which hold values from a lookup table?

Question

I have below lookup table which returns below table list.

{
  "count": 2,
  "value" : [
      {
      "NAME": "table1"
},
{"NAME": "table2"}
]
}

now i want to keep the table name value into a setvariable, so that i can pass these value into a foreach.

i set the expression in setvariable activity

@activity('myLookupActivityName').output.value"

and in foreach, i set below expression

@variables('MyVariableName')

below is the output of set variable

{
  "value": {
    "table_name_list": [
      {
        "NAME": "table1"
      },
      {
        "NAME": "table2"
      }
    ]
  }
}

But foreach taking nothing from the array list.

i dont want table names to be passed directly from lookup table into foreach.

So how can fetch each table name from the setvaraible and pass into foreach?

Answer 1

Your set variable activity should not be like above. Follow the below step-by-step process to achieve your requirement.

First give your file to lookup activity and uncheck the FirstRow in it. Then make sure the created variable is an array type.

In the set variable activity give the expression @activity('Lookup1').output.value for the tables_list variable.

It will give the output like below.

{
    "name": "table_list",
    "value": [
        {
            "NAME": "table1"
        },
        {
            "NAME": "table2"
        }
    ]
}

Then, give the @variables('tables_list') expression to the For-Each activity and use your activities inside it.

Here, for sample I have assigned @item().name to string variable inside for-loop and you can see its working as expected.

Use the below pipeline JSON to build your pipeline:

{
    "name": "pipeline1",
    "properties": {
        "activities": [
            {
                "name": "Lookup1",
                "type": "Lookup",
                "dependsOn": [],
                "policy": {
                    "timeout": "0.12:00:00",
                    "retry": 0,
                    "retryIntervalInSeconds": 30,
                    "secureOutput": false,
                    "secureInput": false
                },
                "userProperties": [],
                "typeProperties": {
                    "source": {
                        "type": "DelimitedTextSource",
                        "storeSettings": {
                            "type": "AzureBlobFSReadSettings",
                            "recursive": true,
                            "enablePartitionDiscovery": false
                        },
                        "formatSettings": {
                            "type": "DelimitedTextReadSettings"
                        }
                    },
                    "dataset": {
                        "referenceName": "source_csv",
                        "type": "DatasetReference"
                    },
                    "firstRowOnly": false
                }
            },
            {
                "name": "Set variable1",
                "type": "SetVariable",
                "dependsOn": [
                    {
                        "activity": "Lookup1",
                        "dependencyConditions": [
                            "Succeeded"
                        ]
                    }
                ],
                "policy": {
                    "secureOutput": false,
                    "secureInput": false
                },
                "userProperties": [],
                "typeProperties": {
                    "variableName": "table_list",
                    "value": {
                        "value": "@activity('Lookup1').output.value",
                        "type": "Expression"
                    }
                }
            },
            {
                "name": "ForEach1",
                "type": "ForEach",
                "dependsOn": [
                    {
                        "activity": "Set variable1",
                        "dependencyConditions": [
                            "Succeeded"
                        ]
                    }
                ],
                "userProperties": [],
                "typeProperties": {
                    "items": {
                        "value": "@variables('table_list')",
                        "type": "Expression"
                    },
                    "isSequential": true,
                    "activities": [
                        {
                            "name": "Set variable2",
                            "type": "SetVariable",
                            "dependsOn": [],
                            "policy": {
                                "secureOutput": false,
                                "secureInput": false
                            },
                            "userProperties": [],
                            "typeProperties": {
                                "variableName": "name",
                                "value": {
                                    "value": "@item().name",
                                    "type": "Expression"
                                }
                            }
                        }
                    ]
                }
            }
        ],
        "variables": {
            "table_list": {
                "type": "Array"
            },
            "name": {
                "type": "String"
            }
        },
        "annotations": []
    }
}