SSIS - converting string to HH:MM:SS 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 ---&gt; 08:46:00
1323 ---&gt; 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 ? &quot;0&quot; + SUBSTRING(ArrTime,1,1) + &quot;:&quot; + SUBSTRING(ArrTime,2,2) + &quot;:00&quot; : SUBSTRING(ArrTime,1,2) + &quot;:&quot; + SUBSTRING(ArrTime,3,2) + &quot;:00&quot;

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



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

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

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.
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 ---&gt; 00:04:00
57 ---&gt; 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 ? &quot;00:0&quot; + ArrTime + &quot;:00&quot; : (LEN(ArrTime) == 2 ? &quot;00:&quot; + ArrTime + &quot;:00&quot; : (LEN(ArrTime) == 3 ? &quot;0&quot; + SUBSTRING(ArrTime,1,1) + &quot;:&quot; + SUBSTRING(ArrTime,2,2) + &quot;:00&quot; : SUBSTRING(ArrTime,1,2) + &quot;:&quot; + SUBSTRING(ArrTime,3,2) + &quot;:00&quot;))

Now I solved the problem and all works fine.
Maybe someone will need such solution ;)