Azure Data Factory: datetime conversion

Rutger Verhaar | Adaptfy 1 Reputation point
2022-11-03T13:20:42.177+00:00

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.743 The 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?

Azure Data Factory
Azure Data Factory
An Azure service for ingesting, preparing, and transforming data at scale.
9,544 questions

1 answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. AnnuKumari-MSFT 30,676 Reputation points Microsoft Employee
    2022-11-04T06:19:40.123+00:00

    Hi @Rutger Verhaar | Adaptfy ,

    Welcome to Microsoft Q&A platform and thanks for posting your question here.

    As I understand your issue, you want to format the datetime value for benchmark column while using Azure data factory pipeline. Please let me know if my understanding is incorrect.

    Kindly try using formatDateTime function to convert the datetime value into specified format . Use the following expression: SELECT * FROM Lead WHERE LastModifiedDate > '@{formatDateTime('2022-11-02T10:27:44.743Z','yyyy-MM-dd HH:mm:ss')}'

    For more details, kindly check : Dates & Timestamps In Azure Data Factory

    Hope this will help. Please let us know if any further queries.

    ------------------------------

    • Please don't forget to click on 130616-image.png or upvote 130671-image.png button whenever the information provided helps you.
      Original posters help the community find answers faster by identifying the correct answer. Here is how
    • Want a reminder to come back and check responses? Here is how to subscribe to a notification
    • If you are interested in joining the VM program and help shape the future of Q&A: Here is how you can be part of Q&A Volunteer Moderators
    0 comments