How to convert string to datetime in copy activity

Hermoine Ron 0

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.

Harishga 5,990 Reputation points Microsoft Vendor

2024-06-19T07:10:43.86+00:00

Hi @Hermoine Ron
Welcome to Microsoft Q&A platform and thanks for posting your question here.

The error you're seeing is a TypeConversionFailure, which occurs when the SQL copy activity tries to convert an empty string value from a text file into a DateTime data type in your SQL database. Since an empty string is not a valid DateTime, the process fails.

To fix this, you need to handle the empty strings in the date columns and convert them to a NULL value that can be accepted by the DateTime data type in SQL.

One way to do this is to use a Derived Column transformation in your data flow to replace empty strings with NULL. You can use an expression like iif(isEmpty(Column_1), null(), Column_1) to check if the field is empty and replace it with a NULL value. In the sink (destination), you should set the mapping to insert NULL values into the table.

By doing this, any empty strings in the date columns will be converted to NULL values, which are compatible with the DateTime data type in your SQL database. This will resolve the TypeConversionFailure error.

Hope this helps. Do let us know if you any further queries.
Harishga 5,990 Reputation points Microsoft Vendor

2024-06-20T12:23:28.2233333+00:00

@Hermoine Ron
Just checking in to see if the below answer provided by @Amira Bedhiafi helped.

If this answers your query, do click Accept Answer and Yes for was this answer helpful. And, if you have any further query do let us know.

1 answer

Amira Bedhiafi 25,866 Reputation points

2024-06-19T07:17:30.1233333+00:00
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'))
Please sign in to rate this answer.

0 comments No comments
Sign in to comment

Use comments to ask for clarification, additional information, or improvements to the question.

Share via

How to convert string to datetime in copy activity

1 answer

Your answer