AZ DATA FACTORY - COPY Activity : 'Incorrect syntax near '>'

Question

Hi,

while i am trying to copy data using below dynamic value.

SELECT * FROM @{pipeline().parameters.P_DS_Object_api}

WHERE lastmodifieddate >= '@{formatDateTime(activity('watermarktable').output.firstRow.Watermarkvalue, 'yyyy-MM-dd HH:mm:ss')}'

i am getting data while preview data but while debut the pipeline getting below error. Please advice
ErrorCode=SqlOperationFailed,'Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException,Message=A database operation failed with the following error: 'Parse error at line: 1, column: 29: Incorrect syntax near '>'.',Source=,''Type=System.Data.SqlClient.SqlException,Message=Parse error at line: 1, column: 29: Incorrect syntax near '>'.,Source=.Net SqlClient Data Provider,SqlErrorNumber=103010,Class=16,ErrorCode=-2146232060,State=1,Errors=[{Class=16,Number=103010,State=1,Message=Parse error at line: 1, column: 29: Incorrect syntax near '>'.,},],'

Answer 1

Your query is dynamically constructed, and the error is occurring due to a parse error in the SQL query.

The error message states there's a problem near >. This is likely due to how the dynamic content is parsed when the pipeline runs. The placeholders inside the SQL statement (like @{pipeline().parameters.P_DS_Object_api} and @{formatDateTime(activity('watermarktable').output.firstRow.Watermarkvalue, 'yyyy-MM-dd HH:mm:ss')}) may be causing this issue.

Before debugging further, it would be useful to log the exact SQL query that's being constructed. This way, you can see how the dynamic content is being parsed and where the error might be occurring. You could use the debug capabilities or add an activity to output the exact SQL being executed.

Whenever you're placing dynamic values inside SQL, especially for dates or strings, make sure they're properly quoted. Your date is already surrounded by single quotes, which is good. However, ensure that the generated date string does not contain any characters that might break the SQL.

Use the SQL query generated from the logging (as described in point 1) and try executing it directly against your database. This will help identify if there's an issue with the SQL itself.

If the P_DS_Object_api parameter can contain spaces, special characters, or SQL reserved keywords, it might break the SQL syntax. Ensure you validate or sanitize this value before inserting it into SQL.

Given the error message, my initial suspicion is that the dynamic parsing is not occurring as expected, and the SQL statement being executed contains some unexpected characters or structure.

Seeing the exact parsed SQL will give a clearer picture. If you can log and provide that, I can provide further assistance.