question

SarveshPandey-9913 avatar image
0 Votes"
SarveshPandey-9913 asked AnnuKumari-MSFT commented

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

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-data-factorysql-server-transact-sqlazure-sql-databaseazure-data-lake-storage
· 3
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.


Probably there is an empty string, which cannot be converted. Maybe it is possible to use null instead of empty strings, or to convert empty to null?


0 Votes 0 ·

Is there any way pipeline doesn't pull null value?

0 Votes 0 ·

Hi @SarveshPandey-9913 ,

Just checking in to see if you got a chance to see the below response. If the suggested response helped you, please click Accept Answer . If you have further questions about this answer, please do Comment.

0 Votes 0 ·

1 Answer

PratikSomaiya avatar image
0 Votes"
PratikSomaiya answered AnnuKumari-MSFT commented

Hello @SarveshPandey-9913

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

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @SarveshPandey-9913 ,
Just checking in to see if you got a chance to see previous response. If the suggested response helped you, please click Accept Answer and kindly upvote the same. If you have further questions about this answer, please do Comment.

0 Votes 0 ·