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?
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.