Share via

Azure Data Factory error parsing datetime string to SQL datetime2 format?

Robin Smits 131 Reputation points
2021-04-07T09:35:15.157+00:00

I'am retrieving data from an API endpoint.

The response JSON contains some date time strings formatted as "2019-08-15T16:56:30Z" which is valid ISO 8601.

When I use a copy pipeline in ADF it fails with the following error.
Operation on target *********** failed: ErrorCode=SqlOperationFailed,'Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException,Message=A database operation failed. Please search error to get more details.,Source=Microsoft.DataTransfer.ClientLibrary,''Type=System.InvalidOperationException,Message=The given value of type String from the data source cannot be converted to type datetime2 of the specified target column.,Source=System.Data,''Type=System.FormatException,Message=Failed to convert parameter value from a String to a DateTime.,Source=System.Data,''Type=System.FormatException,Message=String was not recognized as a valid DateTime.,Source=mscorlib,'

Could this be related to the fact that some datetime strings are empty? I did validate that when there is a datetime string that they all have the same formatting.

What would be a solution or workaround for this issue?

Azure Data Factory
Azure Data Factory
An Azure service for ingesting, preparing, and transforming data at scale.
0 comments
Answer accepted by question author
  1. Robin Smits 131 Reputation points
    2021-04-08T19:25:39.713+00:00

    Hi @Saurabh Sharma Thanks for your feedback. I've tried the mentioned logging and indeed the empty strings are not parseable as DateTime fields.

    With this issue now confirmed I can see how to follow up with a workaround/solution.

2 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Saurabh Sharma 23,866 Reputation points Microsoft Employee Moderator
    2021-04-08T00:50:44.81+00:00

    Hi @Robin Smits ,

    Thanks for using Microsoft Q&A !!
    Yes, I believe it is happening for empty datetime strings? Are you using a copy data activity ? Can you please try to use the fault tolerance setting to skip incompatible rows (See screenshot below) and add logging settings(storage account, folder path etc.) so that the excluded records gets locked at the configured folder path. You can then validate the dates against which the conversion is failing -

    85494-image.png

    1 person found this answer helpful.
    0 comments
  2. Fernando Hidalgo 101 Reputation points
    2022-01-05T17:47:22.76+00:00

    Hi,

    I have found the same problem. @Saurabh Sharma , I have tried what you said and indeed ADF is logging that it cannot convert an empty string to datetime:

    Timestamp,Level,OperationName,OperationItem,Message
    2022-01-05 17:40:56.1757927,Warning,TabularRowSkip,""""","""",""BF"",""Balance Brought Forward"",""0"",""0"",""1000"",","Exception occurred when converting value '' for column name 'Posting Date' from type 'String' (precision:, scale:) to type 'DateTime' (precision:255, scale:255). Additional info: String was not recognized as a valid DateTime."
    2022-01-05 17:40:56.1907944,Warning,TabularRowSkip,""""","""",""CF"",""Balance as at 26 Nov 2021"",""0"",""0"",""10000"",","Exception occurred when converting value '' for column name 'Posting Date' from type 'String' (precision:, scale:) to type 'DateTime' (precision:255, scale:255). Additional info: String was not recognized as a valid DateTime."

    The expected resoult would be a NULL, as defined in the DataSet.

    I don't really know how to log this as a bug. Please, could any of you do it?

    Thanks,

Your answer

Answers can be marked as 'Accepted' by the question author and 'Recommended' by moderators, which helps users know the answer solved the author's problem.