Converting data types in SSIS

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开发者_Go百科) 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?

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.

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],"\"","")