Search code examples
foreachazure-data-factorylookup

Using dataset column values Lookup artifact in a foreach artifact

I'm trying to use a dataset result from a ADF lookup artifact in a foreach. I need to use each column of the result in a query within the FOREACH artifact.

The lookup is linked to the FOREACH and the Items is set to: @activity('LookupBatchInformation').output.value

Within the Copy artifact I use a dynamic data expression to create the query as follows:

SELECT [COL1] ... FROM [dbo].[TABLE] WHERE [COL1] = item().COL1 AND [COL2] = item().COL2

When I Debug the pipeline, I'm getting:

Error { "code": "BadRequest", "message": null, "target": "pipeline//runid/f9f65ff3-9cfc-49d7-8f32-a61d20e27342", "details": null, "error": null }

I can't seem to find an example that uses a lookup artifact as parameters in a FOREACH loop. Anyone know what I'm doing wrong or have a sample that uses dataset rows/columns in a FOREACH loop?

Thank you

Solution

  • Error { "code": "BadRequest", "message": null, "target": "pipeline//runid/f9f65ff3-9cfc-49d7-8f32-a61d20e27342", "details": null, "error": null }

    The above error occurs when there is a syntax error in the query and dynamic expression.

    Build your query using @concat() function in the dynamic expression like below.

    @concat('select [<column_name1>] from dbo.[one] WHERE [<column_name1>] = ''',item().<column_name1>,'''AND [<column_name2>] =''',item().<column_name2>,'''')

    enter image description here

    It will give expected results.

    enter image description here