how to call a for each loop item in a query on a dataset in a lookup in ADF

HideyoshiAkaio 220 Reputation points
2024-01-18T17:43:28.27+00:00

I'm setting up a pipeline in Azure Data Factory and attempting to employ a lookup activity for querying an Azure SQL dataset like this.User's image

User's image

The lookup activity resides within a for-each loop. My intention is to retrieve a value from the source dataset queried in the lookup for each iteration of the for-each loop. Despite my attempts to test this by previewing the data, it is not functioning, and I encounter the following message: Any suggestions on how to reference a for-each loop item in a dataset query within a lookup in ADF?

Azure Data Factory
Azure Data Factory
An Azure service for ingesting, preparing, and transforming data at scale.
9,126 questions
{count} votes

Accepted answer
  1. Smaran Thoomu 6,655 Reputation points Microsoft Vendor
    2024-01-19T08:01:59.2966667+00:00

    Hi @HideyoshiAkaio ,

    Welcome to Microsoft Q&A forum and thanks for reaching out.

    Data preview in ADF is used for checking the inserting data is correct or not. So, when we use preview which involves dynamic expressions, it does not take those expressions from the pipeline run and it asks us to manually enter the value for it. So, that's why in the above it is asking a sample item() expression value to give the data preview in the lookup. When I gave my item() value in that I got correct preview as below: enter image description here Preview of lookup that I got for that particular item(): enter image description here Your approach is fine and when you debug the pipeline with required query in lookup, you can get the desired result. please go through my sample demonstration: Array variable for ForEach: enter image description here look up inside ForEach with query: enter image description here Result after debug pipeline: enter image description here You can see that I got the same result in the first iteration of ForEach, which is in look up preview above. So, for preview, we have to give a sample value to check our result.

    Hope this helps. Do let us know if you any further queries.


    If this answers your query, do click Accept Answer and Yes for was this answer helpful. And, if you have any further query do let us know.


0 additional answers

Sort by: Most helpful