ADF COPY ACTIVITY FAILS DUE TO TIME DATATYPE COLUMN

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

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.
6,266 questions
No comments
{count} votes

2 answers

Sort by: Most helpful
  1. answered 2022-06-21T16:07:37.23+00:00
    Subashri Vasudevan 4,296 Reputation points

    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. answered 2022-07-01T05:49:25.187+00:00
    AnnuKumari-MSFT 19,116 Reputation points Microsoft Employee

    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