Dynamic query in azure data factory based on array variable

Question

I want to create a copy activity, with a dynamic query, based on an array variable, inside azure data factory, for example I only want to load the information of the IDs that are in the table A of the database X inside the table B in the Y database.

The code in the set variable activity associated to the variable ID_Variable would be: Select distinct ID from tableA;

And I tried it like this in the copy activity: Select * from tableB where ID in (@variables('ID_Variable'));

But it didn't work, thanks in advance

Answer 1

This is because that IN clause needs a comma-separated list of quoted values such as(1,2,3,4), but array value in ADF is [1,2,3,4]. So it didn't work.

You need to change array value to comma-separated values enclosed in parentheses. The Easy way is to change your first SQL Select distinct ID from tableA to

select stuff(
  (
    select distinct ','''+ CONVERT(VARCHAR(32), ID) +''''
    from   tableA
    for    xml path('')
  )
  , 1, 1, ''
) as in_clause

if it's possible. Otherwise you need to do this with extra activity in ADF. You can refer to this error called Incorrect Syntax near in Azure DataFactory.