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):
And this last step makes the package falling. Here is error message:
[Data Conversion ] 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?