Azure Data Fcatory: String was not recognized as a valid DateTime.Couldn't store <> in Date_column Column

Sarvesh Pandey 71 Reputation points

I have a Copy data Activity which pull data from Source Parquet file to Sink Azure SQL database. while doing so its throws an error of
"Failure happened on 'Sink' side. ErrorCode=UserErrorInvalidDataValue,'Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException,Message=Column 'Date_column' contains an invalid value ''.,Source=Microsoft.DataTransfer.ServiceLibrary,''Type=System.ArgumentException,Message=String was not recognized as a valid DateTime.Couldn't store <> in Date_column Column. Expected type is DateTime.,Source=System.Data,''Type=System.FormatException,Message=String was not recognized as a valid DateTime.,Source=mscorlib,'

I have run distinct command over the Date_column column below is the output -
03/27/2022 07:42:12
03/29/2022 08:05:27
03/22/2022 07:39:05

03/23/2022 08:03:05

Distinct count is 5.

I have checked the sink table. null value is allowed for date_column
I think it causing issue while convert null value in datetime. Please help me on this!

Azure SQL Database
Azure Data Lake Storage
Azure Data Lake Storage
An Azure service that provides an enterprise-wide hyper-scale repository for big data analytic workloads and is integrated with Azure Blob Storage.
1,190 questions
Azure Data Factory
Azure Data Factory
An Azure service for ingesting, preparing, and transforming data at scale.
8,477 questions
{count} votes

2 answers

Sort by: Most helpful
  1. Pratik Somaiya 4,126 Reputation points

    Hello @Sarvesh Pandey

    I don't think there's a NULL value in your case but a Blank value which can't be converted into string

    Can you copy the value and paste it in an editor, so it will show if there are blank spaces in the string, that can be the reason why it didn't convert it into DateTime

  2. John Ryan 1 Reputation point

    Hi all, anyone find a solution to this error yet? experiencing same in adf copy activities.