CODEWITHSUNDEEP
sqlsql-serverssisssis-data-types
Raj More
Raj More

Reputation: 48016

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

Upvotes: 0

Views: 5031

Answers (2)

John
John

Reputation: 1522

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.

Upvotes: 4

il_guru
il_guru

Reputation: 8508

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

Upvotes: 2

Related Questions