Syntax Error in Azure Data Factory SOQL Query WHERE LastModifiedDate >= datetime

Question

I'm working in Azure Data Factory V2, attempting to query from a Salesforce object where the LastModifiedDate in the object is greater than or equal to a recent date. I've been receiving syntax errors on extremely simple SOQL queries, queries that work just fine in Salesforce Workbench. Below, I've listed examples of queries I've run in the ADF Copy Data activity, along with the errors I received when debugging the pipeline.

Query 1

 Select Id from "Object" WHERE LastModifiedDate >=
 @{formatDateTime(activity('Yesterday').output.firstRow.Yesterday,'yyyy-MM-ddTHH:mm:ssZ')}

Result 1

{
    "errorCode": "2200",
    "message": "Failure happened on 'Source' side. ErrorCode=UserErrorOdbcOperationFailed,'Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException,Message=ERROR [HY000] [Microsoft][Salesforce] (120) SOQL_FIRST mode prepare failure:\nSOQL error: [Microsoft][Salesforce] (30) Syntax error or access violation when parsing SOQL.\nSQL error: [Microsoft][SQLEngine] (31480) syntax error near 'Select Id from \"Object\" WHERE LastModifiedDate >= 2018-08-13T16<<< ??? >>>:05:06Z'.,Source=Microsoft.DataTransfer.ClientLibrary.Odbc.OdbcConnector,''Type=System.Data.Odbc.OdbcException,Message=ERROR [HY000] [Microsoft][Salesforce] (120) SOQL_FIRST mode prepare failure:\nSOQL error: [Microsoft][Salesforce] (30) Syntax error or access violation when parsing SOQL.\nSQL error: [Microsoft][SQLEngine] (31480) syntax error near 'Select Id from \"Object\" WHERE LastModifiedDate >= 2018-08-13T16<<< ??? >>>:05:06Z'.,Source=Microsoft Salesforce ODBC Driver,'",
    "failureType": "UserError",
    "target": "Event Fix Pipeline"
}

Query 2

SELECT Id FROM "Object" WHERE LastModifiedDate >= 2018-08-14T00:00:00Z

Result 2

{
    "errorCode": "2200",
    "message": "Failure happened on 'Source' side. ErrorCode=UserErrorOdbcOperationFailed,'Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException,Message=ERROR [HY000] [Microsoft][Salesforce] (120) SOQL_FIRST mode prepare failure:\nSOQL error: [Microsoft][Salesforce] (30) Syntax error or access violation when parsing SOQL.\nSQL error: [Microsoft][SQLEngine] (31480) syntax error near 'SELECT Id FROM \"Object\" WHERE LastModifiedDate >= 2018-08-14T00<<< ??? >>>:00:00Z'.,Source=Microsoft.DataTransfer.ClientLibrary.Odbc.OdbcConnector,''Type=System.Data.Odbc.OdbcException,Message=ERROR [HY000] [Microsoft][Salesforce] (120) SOQL_FIRST mode prepare failure:\nSOQL error: [Microsoft][Salesforce] (30) Syntax error or access violation when parsing SOQL.\nSQL error: [Microsoft][SQLEngine] (31480) syntax error near 'SELECT Id FROM \"Object\" WHERE LastModifiedDate >= 2018-08-14T00<<< ??? >>>:00:00Z'.,Source=Microsoft Salesforce ODBC Driver,'",
    "failureType": "UserError",
    "target": "Event Fix Pipeline"
}

What could be causing this syntax error?

I don't know much about Azure, sorry... What's with the quotes around "Object"? SF uses table names directly. Have you tried with simple SELECT Id, Name FROM Account LIMIT 5? Are you sure error sits in date formatting? — eyescream

David David · Accepted Answer · 2018-08-16T04:33:48

Removing the quotation marks around Object seems to solve your problem.

SELECT Id FROM Object WHERE LastModifiedDate >= 2018-08-14T00:00:00Z.

I tested the query with quotation marks in Salesforce Developer Console and got "Unknown error parsing query", so I believe it's an invalid SOQL query with the quotation marks.

Syntax Error in Azure Data Factory SOQL Query WHERE LastModifiedDate >= datetime

1 Answers