0

I am trying to build a fully parametrised pipeline template in ADF. With the work I have done so far, I can do a full load without any issues but when it comes to delta load, it seems like my queries are not working. I believe the reason for this is that my "where" statement looks somewhat like this:

SELECT @{item().source_columns} FROM @{item().source_schema}.@{item().source_table} 
WHERE @{item().source_watermarkcolumn} > @{item().max_watermarkcolumn_loaded} AND @{item().source_watermarkcolumn} <= @{activity('Watermarkvalue').output.firstRow.max_watermarkcolumn_loaded}

where the 'max_watermarkcolumn_loaded' is a datetime format and the 'activity' output is obviously a string format.

Please correct me if my assumption is wrong and let me know what I can do to fix.

EDIT: screenshot of the error enter image description here

ADF is picking a date from SQL column 'max_watermarkcolumn_loaded' in this format '"2021-09-29T06:11:16.333Z"' and I think thats where the problem is.

newbie
  • 53
  • 10
  • Do you get any error? if yes, share the error message. – Aswin Jan 24 '23 at 03:34
  • @Aswin i Have edited my post for clarification. Thanks – newbie Jan 24 '23 at 04:46
  • Wrap these parameters within single quotes `'@{item().max_watermarkcolumn_loaded}'` and `'@{activity('Watermarkvalue').output.firstRow.max_watermarkcolumn_loaded}'` – Aswin Jan 24 '23 at 04:50
  • `SELECT @{item().source_columns} FROM @{item().source_schema}.@{item().source_table} WHERE @{item().source_watermarkcolumn} > '@{item().max_watermarkcolumn_loaded}' AND @{item().source_watermarkcolumn} <= '@{activity('Watermarkvalue').output.firstRow.max_watermarkcolumn_loaded}'` – Aswin Jan 24 '23 at 04:51

1 Answers1

0

I tried to repro this error. I gave the parameter without single quotes to a sample Query. enter image description here

Wrap the date parameters with single quotes.

Corrected Query

SELECT @{item().source_columns} FROM 
@{item().source_schema}.@{item().source_table} 
WHERE @{item().source_watermarkcolumn} > 
'@{item().max_watermarkcolumn_loaded}' AND 
@{item().source_watermarkcolumn} <= 
'@{activity('Watermarkvalue').output.firstRow.max_watermarkcolumn_loaded}'

With this query, pipeline is run successfully. enter image description here

Aswin
  • 4,090
  • 2
  • 4
  • 16
  • thank you! it worked. Could you please explain the significance of the single quotes around parameters? – newbie Jan 24 '23 at 11:26
  • WIthout giving single quotes your query will be `SELECT col1 FROM dbo.tab1 WHERE watermarkcolumn>2010-10-10 `. Thats the reason it is necessary to wrap them in single quotes – Aswin Jan 24 '23 at 11:58