I have a pipeline with a copy activity with Salesforce as Sink and ADLS as source. I want the pipeline to copy only the records from Salesforce that are larger than the MAX(LastModifiedDate) from ADLS (Delta loads).
I have a control table in SQL which stores the lastupdated date for ADLS runs.When trying to run my pipeline I get the following error:
Failure happened on 'Source' side. ErrorCode=UserErrorUnclassifiedError,'Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException,Message=Odbc Operation Failed.,Source=Microsoft.DataTransfer.ClientLibrary.Odbc.OdbcConnector,''Type=System.Data.Odbc.OdbcException,Message=ERROR [22018] [Microsoft][Support] (40550) Invalid character value for cast specification.,Source=Microsoft Salesforce ODBC Driver,'
Before the copy activity I perform two lookups, 1 to retrieve the lastupdate date in my control table and 1 to retrieve the max value for LastModifiedDate in salesforce.
The values that are returned are as follows:
- ADLS/SQL
"ADLSWatermark": "2022-11-02T10:27:44.743Z" - Salesforce
"SalesforceWatermarkvalueDT": "2022-11-03T09:45:50Z"
When configuring the copy activity for sink I connect to salesforce and run the following query (I am hardcoding the datetime values here for testing:
SELECT * FROM Lead WHERE LastModifiedDate > '2022-11-02T10:27:44.743Z'
This as a result throws the error provided above. When I update the timestamp in the where clause to this:
2022-11-02 10:27:44.743The copy activity runs without any issue.
For Sink I simply selected a folder in my ADLS container.
What I noticed is that both formats of date are different. Does anyone know how to make them equal? Or any other work around/solution?
