Azure Data Factory - How do I iterate and read records in a CSV file using a ForEach loop?

Question

I have a requirement to generate a unique 'guid' for each row in the dataverse, and for that, I'm using an additional column in the copy activity as shown below.

Since it generates the same guid for every row, I'm trying to use a lookup and iterate through a "For Each" loop as shown below.

In the "For Each" loop, I'm using this expression to retrieve the values:

@activity('Lookup1').output.value

However, I'm encountering this error:

This is a sample output from the lookup activity: I'm using .csv file as the source

{
    "firstRow": {
        "Bill-to Customer Name": "****",
        "Bill-to Customer No_": "****",
        // ... (other fields)
        "ProjectCustomer_FullName": "****",
        "ProjectCustomer_ID": "****"
    },
    "effectiveIntegrationRuntime": "****",
    "billingReference": {
        "activityType": "****",
        // ... (other billing reference fields)
    },
    "durationInQueue": {
        "integrationRuntimeQueue": ****
    }
}

How to resolve this issue ? Appreciate your help on this.

Answer 1

To iterate the CSV file using ForEach, you need to Disable the First row only in the lookup. I got same error, when I used expression @activity('Lookup1').output.value in the ForEach without disabling the First row only in the lookup.

Because, First row only will give the First row of csv as the object.

Disabling it will give the Lookup output array from the csv and you can see My pipeline ran without any errors after disabling it.

Use the same @activity('Lookup1').output.value in the ForEach to use the lookup output array. Inside ForEach, you can access this array values by @item().<property_name> or @item()['<property_name>'].