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

Do you have a Lookup activity to run this SQL `Select distinct ID from tableA` and then pass it's output to your variable ID_Variable? If no, can you provide screenshots of your pipeline and your ID_Variable sample data? — Steve Johnson, Apr 15 '21 at 05:50

score 0 · Answer 1 · answered Apr 16 '21 at 09:27

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.

Dynamic query in azure data factory based on array variable

1 Answers1