How to use output on one lookup activity to another lookup activity in ADF?

Question

I have two SQL servers and both server has same table under the name of "[TABLE-A]". What I need to do is, I want to maintain the same exact records in [SERVER2].[TABLE-A] as it is in the [SERVER1].[TABLE-A]. Whenever any record has been inserted or deleted [SERVER1].[TABLE-A] I need to do the same thing in [SERVER2].[TABLE-A]. So I had created two lookup activity under the name of 'Lookup1' and 'Lookup2'. From Lookup1 I am getting all the unique Id's which are present in [SERVER1].[TABLE-A] from the below query.

SELECT DISTINCT Id from [SERVER1].[TABLE-A]

Which gives me a column which contains all 70 distinct Id's.

My Idea is to use this Id's in Lookup2 and delete Id's which are not present in the Lookup1 output.

DELETE FROM [SERVER2].[TABLE-A] WHERE Id NOT IN ('@{activity('Lookup1').output.value[0].Id}')

But I am not able to put those 70 Id's in ('@{activity('Lookup1').output.value[0].Id}') It takes only 1 value from those 70 since I specified that way. How can I put those 70 Id's in my Lookup2 query?

Answer 1

Lookup activity output will be of array type. Sample lookup activity output data:

{
    "value": [
        {
            "id": 1
        },
        {
            "id": 3
        },
        {
            "id": 4
        }
    ]
  }

The expression @{activity('Lookup1').output.value[0].Id} will give only the first value. In order to give all the values from lookup activity to the sql query, there are few changes to be done in the lookup1 data

• {"id": and } are to be removed from the data and [ and ] are to be replaced with ( and ) respectively. Thus, the above data will become like (1,3,4).

• To do this , a variable v1 of string type is taken and the value for that variable is set as @{activity('Lookup1').output.value}.

• Variable v2(string type) is taken and in this variable all the unwanted data is removed from v1. The value is given as @{replace(replace(replace(replace(variables('v1'),'{"id":',''),'}',''),'[','('),']',')')}.

• Then lookup activity2 is taken and query is given as

select * from emp1 where id in @{variables('v2')}

This will select the rows from emp1 which are available in lookup1 table. Delete sql command can also be written similarly.