Azure Data Factory Error: "incorrect syntax near"

I'm trying to do a simple incremental update from an on-prem database as source to Azure SQL database based on a varchar column called "RP" in On-Prem database that contains "date+staticdescription" for example: "20210314MetroFactory"

1- I've created a Lookup activity called Lookup1 using a table created in Azure SQL Database and uses this Query

"Select RP from SubsetwatermarkTable"

2- I've created a Copy data activity where the source settings have this Query

"Select * from SourceDevSubsetTable WHERE RP NOT IN '@{activity('Lookup1').output.value}'"

When debugging -- I'm getting the error:

Failure type: User configuration issue

Details: Failure happened on 'Source' side. 'Type=System.Data.SqlClient.SqlException,Message=Incorrect syntax near '[{"RP":"20210307_1Plant 1KAO"},{"RP":"20210314MetroFactory"},{"RP":"20210312MetroFactory"},{"RP":"20210312MetroFactory"},{"RP":"2'.,Source=.Net SqlClient Data Provider,SqlErrorNumber=102,Class=15,ErrorCode=-2146232060,State=1,Errors=[{Class=15,Number=102,State=1,Message=Incorrect syntax near '[{"RP":"20210311MetroFactory"},{"RP":"20210311MetroFactory"},{"RP":"202103140MetroFactory"},{"RP":"20210308MetroFactory"},{"RP":"2'.,},],'

Can anyone tell me what I am doing wrong and how to fix it even if it requires creating more activities.

Note: There is no LastModifiedDate column in the table. Also I haven't yet created the StoredProcedure that will update the Lookup table when it is done with the incremental copy.

Steve is right as to why it is failling and the query you need in the Copy Data.

As he says, you want a comma-separated list of quoted values to use in your IN clause.

You can get this more easily though - from your Lookup directly using this query:-

select stuff(
  (
    select ','''+rp+''''
    from   subsetwatermarktable
    for    xml path('')
  )
  , 1, 1, ''
) as in_clause

The sub-query gets the comma separated list with quotes around each rp-value, but has a spurious comma at the start - the outer query with stuff removes this.

Now tick the First Row Only box on the Lookup and change your Copy Data source query to:

select *
from   SourceDevSubsetTable
where  rp not in (@{activity('lookup').output.firstRow.in_clause})