I am trying to read flat file with some data and I need to convert some columns to time type in the format of HH:MM:SS.

Below is the example how they are stored in the file (on the left) and how I want them to see in the database (on the right).

846 ---> 08:46:00

1323 ---> 13:23:00

I am performing following transformations:

  • if NA value appears, I am converting it to NULL (with Derived column)

  • then I am concatenating it to string in format of HH:MM:SS (using Derived Column)

    LEN(ArrTime) == 3 ? "0" + SUBSTRING(ArrTime,1,1) + ":" + SUBSTRING(ArrTime,2,2) + ":00" : SUBSTRING(ArrTime,1,2) + ":" + SUBSTRING(ArrTime,3,2) + ":00"

  • then I am converting it to the type DT_DBTIME2 with scale of 0 (using Data Conversion):

enter image description here

And this last step makes the package falling. Here is error message:

[Data Conversion [20]] Error: Data conversion failed while converting column "ArrTime" (166) to column "Copy of ArrTime" (27). The conversion returned status value 2 and status text "The value could not be converted because of a potential loss of data.".

I am not very experienced in SSIS, but I really cannot see any reason for such an error.

When I tried to perform it without conversion, everything had gone fine.

Can anybody help me with that?



1 Answer 1



It was related to specific construction of dataset.

if the time was in range from 00:00:00 to 00:59:00 it was presented as follows (examples):

4 ---> 00:04:00

57 ---> 00:57:00

So the previously posted code did not even take such a case into consideration.

The code solving the problem is following:

LEN(ArrTime) == 1 ? "00:0" + ArrTime + ":00" : (LEN(ArrTime) == 2 ? "00:" + ArrTime + ":00" : (LEN(ArrTime) == 3 ? "0" + SUBSTRING(ArrTime,1,1) + ":" + SUBSTRING(ArrTime,2,2) + ":00" : SUBSTRING(ArrTime,1,2) + ":" + SUBSTRING(ArrTime,3,2) + ":00"))

Now I solved the problem and all works fine.

Maybe someone will need such solution ;)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.