Call item() from within query of a lookup on a dataset within a for each loop

Question

I'm creating a pipeline in Azure Data Factory and I'm trying to use a lookup activity to query an Azure SQL dataset as so:

The lookup activity is inside a for each loop. My hope is to get a value from the source dataset being queried in the lookup for every item in the for each loop. However, when I preview the data to test this, it does not work and I get this message:

Does anyone have any ideas as to how to call a for each loop item in a query on a dataset in a lookup in ADF?

Thanks,

Carolina

EDIT:

I've changed tact and tried to use a stored procedure but I'm still having the exact same issue. It seems like I can't call the for loop item with a query of stored procedure. Does anyone know a way around this or how to call the item properly?

Answer 1

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:

Preview of lookup that I got for that particular item():

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:

look up inside ForEach with query:

Result after debug pipeline:

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.