CODEWITHSUNDEEP
sqlcsvdatetimeazure-data-factory
Alix
Alix

Reputation: 61

String To DateTime Error in Azure Data Factory

I'm trying to import data from a CSV to Azure SQL and there seems to be an issue with ADF importing a datetime column. I'm using ADF V2 and all the online help seems to show fixes for ADF V1.

The date column is in dd/mm/yyyy hh:mm:ss format in the source CSV and the destination Azure SQL is datetime format, so should work perfectly, but it doesn't. ADF seems to collect all data in the CSV as a string and then throw out an error saying cant convert string to datetime.

ErrorCode=TypeConversionFailure,Exception occurred when converting value '04-Apr-22 00:00:00' for column name 'DateTime' from type 'String' (precision:, scale:) to type 'DateTime' (precision:23, scale:3). Additional info: String was not recognized as a valid DateTime.

I have tried using the type version setting in the Mapping, but that doesn't work. I've tried every datetime format I can think of.

enter image description here

Any help solving this would be much appreciated

Upvotes: 4

Views: 4836

Answers (3)

Ankur Gupta
Ankur Gupta

Reputation: 1

This error is happening where you have empty value in concerned column in CSV.

You can use fault tolerance in settings of copy activity and give a logs path to know what exact rows are responsible for the error.

There is a solution proposed for this kind of problem in this link Data Factory copy csv to SQL cannot convert empty data

enter image description here

Upvotes: 0

dantz
dantz

Reputation: 57

Base on the error the data being converted was this: '04-Apr-22 00:00:00' So your date time format should be dd/MMM/yyyy hh:mm:ss

Upvotes: 1

Noelle
Noelle

Reputation: 151

Have you tried it without setting a DateTime format in the mapping settings?

enter image description here

Upvotes: 0

Related Questions