SSIS – converting string to HH:MM:SS

ssistime

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?

Greetings,

Chris.

Best Answer

Update:

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 ;)

Related Question