azure data factory - Copy data activity mapping "hh:mm" to SQL "Time" column

Question

I am trying to ingest a CSV file into Azure SQL with a column with data type of time. The value in the csv file is in this format "hh:mm". I have setup my conversion setting into these settings:

But then I am having this error:

ErrorCode=TypeConversionFailure,Exception occurred when converting value '23:55' for column name 'systime' from type 'String' (precision:, scale:) to type 'TimeSpan' (precision:255, scale:7). Additional info: Input string was not in a correct format.

Not sure how to solve this issue. Can someone please help me. Thanks in advance.

Answer 1

I gave the Timespan format as hh\:mm and tried to debug the pipeline that has the copy activity to copy data from csv file to SQL database.

Mapping settings:

• Received the same error as in the question's image.

"message": "ErrorCode=TypeConversionFailure,Exception occurred when converting value '24:00' for column name 'Datecolumn1' from type 'String' (precision:, scale:) to type 'TimeSpan' (precision:255, scale:7). Additional info: The TimeSpan could not be parsed because at least one of the numeric components is out of range or contains too many digits."

This is because one of the time data in source csv is 24:00. hh which represents the hour in the timespan and its value ranges from 0 to 23 only. To resolve this issue, you can modify the source data to remove any values that are out of range for the hour component of the Time data type. In this case, you can remove any values that are equal to or greater than 24. You can also use mapping dataflow in ADF to replace '24:00' with '00:00'.

Steps to do in dataflow:

• Take the source csv dataset in source transformation
• Then take the derived column transformation and give the expression as iif(valuation_time == '24:00', '00:00', valuation_time )
• Take the sink transformation with SQL dataset.