question

Raj More avatar image
Raj More asked

Error converting String to DB_DateTime 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?
ssisetldata-conversiontransform
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Daniel Ross avatar image
Daniel Ross answered
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.
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

aRookieBIdev avatar image
aRookieBIdev answered
Hi , You should use a derived column convert the string input to the format YYYY-MM-DD HH:MIS:SS and the do a dataconversion on it . Thanks, Kannan
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Blackhawk-17 avatar image
Blackhawk-17 answered
You could run a test with the ForceTruncate property set and see if there actually is a loss of data.
2 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

@BlackHawk-17 Good idea. Is it possible that the reason SSIS thinks that there is loss of data because when it tries to convert it sees that there is no time information present, just the date portion?
0 Likes 0 ·
@Oleg - that's where my thoughts were going.
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.