Azure Data Factory Error: "incorrect syntax near"

Question

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.

Answer 1

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})

Answer 2

The result of @activity('Lookup1').output.value is an array like your error shows

[{"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'.,},]

However, your SQL should be like this:Select * from SourceDevSubsetTable WHERE RP NOT IN ('20210307_1Plant 1KAO','20210314MetroFactory',...).

To achieve this in ADF, you need to do something like this:

1. create three variables like the following screenshot:

2. loop your result of @activity('Lookup1').output.value and append 'item().RP' to arrayvalues:

expression:@activity('Lookup1').output.value

expression:@concat(variables('apostrophe'),item().RP,variables('apostrophe'))

3.cast arrayvalues to string and add parentheses by Set variable activity expression:@concat('(',join(variables('arrayvalues'),','),')')

4.copy to your Azure SQL database expression:Select * from SourceDevSubsetTable WHERE RP NOT IN @{variables('stringvalues')}