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 Kannan said, use a derived column, but leave the source as a string. Then add your derived column transform and enter dateFormat as the Derived Column Name, leave the next option as add as new column, enter the expressions as; (DT_DBTIMESTAMP)[Date] Where Date is the input from the csv file, then the data type will be database timestamp [DT_DBTIMESTAMP], and you should be fine to insert it. If it still fails, then try one of the other OLEDB oracle providers.