ADF COPY ACTIVITY FAILS DUE TO TIME DATATYPE COLUMN

Shaista Lubna 1 Reputation point
2022-06-20T21:14:00.613+00:00

I am trying to copy data from SQL Server to Snowflake using ADF Pipeline, I am using a copy activity, my source and destination column data types all match , my source column "LastUpdateTime" in SQL Server is a TIME data type . My destination column "LastUpdateTime" in Snowflake is also TIME, but it errors out by appending an additional "0:", This is the error I get

ErrorCode=UserErrorOdbcOperationFailed,'Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException,Message=ERROR [22007] Time '0:09:40:57.9433333' is not recognized

My source column value is "09:40:57.9433333' . Not sure why ADF is interpreting an additional "0:", I also tried explicit conversion of my column using CAST(LastUpdateTime as TIME) by using the Query instead of direct table source, even though the source column data type is already time, it still fails .

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

2 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Suba Balaji 11,186 Reputation points
    2022-06-21T16:07:37.23+00:00

    Hi @Shaista Lubna ,

    Thanks for posting your query on Microsoft Q&A platform

    Just one clarification,

    Did you copy and paste the copy activity from some other pipeline or you created it afresh?

    Thanks

  2. AnnuKumari-MSFT 30,361 Reputation points Microsoft Employee
    2022-07-01T05:49:25.187+00:00

    Hi @Shaista Lubna ,

    Additionally to what Subhashri mentioned above, you can try using type conversion settings if date columns in your table have similar format .

    If all the date fields are having different date formats like (yyyy.MM.dd , yyyy.MM.dd HH:mm, yyyy.MM.dd HH:mm:ss) then try using data flows to achieve the requirement.

    Check this thread for more details.

    216873-image.png

    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