How to convert string to datetime in copy activity

Question

I have delimited text files and I am trying to copy that into my sql db. I have some date columns and it is empty in certain cases, so my copy activity is throwing the following error, how to fix this

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

In my destination the datatype of this column is date. How to fix this issue.

Answer 1

I think you have an issue handling empty data strings so in this case you need to add a Derived Column to create a new column or replace the existing date column with a transformation that converts empty strings to NULL or a default date.

For example :

iif(isNull({your_date_column}) || {your_date_column} == '', toDate('1900-01-01'), toDate({your_date_column}, 'yyyy-MM-dd'))