0

as the title states, in an Azure Data Factory pipeline I am trying to use the results of a Lookup activity in a WHERE condition of a query against Salesforce.

To be more detailed, the Lookup activity has the option 'Select only first row' NOT selected and it looks as follow:

SELECT id FROM staging_table

Then I am trying to pass the output to a Copy Data activity that runs a query against Salesforce using the output of the Lookup in a condition that looks like WHERE Id IN @output, however any kind of syntax I try for the activity output results in some errors, I only managed to get it working for the first result of the array.

Thank you for any help you may provide!

carloab
  • 37
  • 6

1 Answers1

0

This is because you will get this result like this [{"id":1},{"id":2}] when you don't check Select only first row option. However, you need this output (1,2) in your SQL WHERE Id IN @output. So you need to get the output of your Lookup activity (1,2) in your first SQL or extract from [{"id":1},{"id":2}].

Here is a similar question about this, you can try the solution. error called Incorrect Syntax near in Azure DataFactory

Steve Johnson
  • 8,057
  • 1
  • 6
  • 17