Salesforce SOQL in ADF using variable

Question

I'm trying to use the output from a dataflow (a comma separated string of IDs), then set as a variable, and pass the variable into a SOQL query IN clause. However, the SOQL clause won't accept the string. Somehow it turns to an array.

variable output:

Pipeline:

Dataflow output

Dataflow and sink preview:

SOQL Query:

I have tried to write the output from dataflow to the variable but the SOQL query won't accept the variable. Unsure what part went wrong. In the data flow I removed '[',']' and replaced " with ' .

Answer 1

The issue is with the format of the variable output. Variable List1 has both key and value. In order to get only the value, give the expression as @activity('<data-flow-name>').output.runStatus.output.<sink-name>.value[0].IDList.

Replace <data-flow-name> and <sink-name> with the dataflow name and sink transformation name. This will ensure that key is removed and only value is available in the variable.

In the copy activity, change the value of the IN clause to the following expression:

select <column-names> from <table-name>
 where <col-name> in (@{variables('List1')})
.....