Converting data types in SSIS

Question

I get an input CSV file that I have to upload to my oracle database.

Here is some sample data

ContractId, Date, HourEnding, ReconciledAmount
13860,"01-mar-2010",1,-.003
13860,"01-mar-2010",2,.923
13860,"01-mar-2010",3,2.542

I have to convert the incoming column to DB_TIMESTAMP (to match the structure in the destination table).

But when I use Data Conversion to convert, I get an error

Data conversion failed while converting column "Date" (126) to column "Date" (496). The conversion returned status value 2 and status text "The value could not be converted because of a potential loss of data.

What should I do to be able to properly convert this data?

Answer 1

The problem is with the quotation marks ["] in the file. You should remove them from the file or add a Derived Column before the Data Conversion component to remove the " with the expression

REPLACE([TextDateColumn],"\"","")

Answer 2

What you could do in this situation is change the Text Qualifer in your Flat File connection to be a single double quote (").

This will cause SSIS to interperet

13860,"01-mar-2010",1,-.003

as

13860,01-mar-2010,1,-.003

This also has the added bonus of being able to catch any embedded commas in your data if they are also qualfied with quotes.