I amgot stuck with an issue. I need to pass an array value in my where clause in copy activity.

Question

I have one requirement where I have to get Ids from one table in a lookup activity and use the result set of that lookup activity in a query to filter the data in my copy activity.

I used first a lookup activity like which is working fine .

then used a set valiable activity to store the resultset in a variable but then when I am using the below query to retrive the data

. inside look up activity ---> Select Id from table1

then inside set variable --> @array(activity(lookup.output.value))-->working fine( getting output data in Json format)

Then inside the copy activity writing the below query

Select* from table name where id IN @activity(setvariable).value. ---> Not working

Please help on the this. What should I write in where clause.

Answer 1

I think you need to transform the array into a format that the SQL query can understand.

When you use @activity('lookup').output.value, it returns an array of JSON objects like so:

[
  { "Id": "1" },
  { "Id": "2" },
  { "Id": "3" }
]

SQL expects the IN clause to be in the form of (1, 2, 3). You'll have to transform your JSON array into a string that looks like that.

In the Set Variable activity, you can use this expression to transform the JSON array into a string like '1,2,3':

 @string(join(',', array(map(activity('YourLookupActivityName').output.value, item => item.Id))))

This will give you a string variable (let's call it `IdsList`) that you can use in your SQL query.

Then, In your Copy activity, you'll then have to form the SQL query like so:

    Select * from TableName where id IN (@{variables('IdsList')})

Please try and tell us !