2

I'm facing a problem in a package to import some data from a MySQL table to Oracle table and MS SQL Server table. It works well from MySQL to SQL Server, however I get an error when I want to import to Oracle.

The table I want to import contains an attribute (unitPrice) of data type DT_R8.

enter image description here

The destination data type for Oracle is a DT_NUMBERIC as you can see in the capture.

enter image description here

I added a conversion step to convert the unitPrice data from DT_R8 to DT_NUMERIC.

enter image description here

It doesn't work, I get the following error.

enter image description here

I found the detail of the error :

An ORA-01722 ("invalid number") error occurs when an attempt is made to convert a character string into a number, and the string cannot be converted into a valid number. Valid numbers contain the digits '0' through '9', with possibly one decimal point, a sign (+ or -) at the beginning or end of the string, or an 'E' or 'e' (if it is a floating point number in scientific notation). All other characters are forbidden.

However, I don't know how to fix.

EDIT : I added a component to redirect rows/errors to an Excel file.

enter image description here

The following screenshot show the result of the process including errors :

enter image description here

By browsing the only 3000 rows recorded, It seems the process accept only int values no real. So if the price is equal to 10, it's OK but if it's 10,5 it's failed.

Any idea to solve this issue ?

K4timini
  • 711
  • 2
  • 14
  • 34
  • Are you 100% positive the MySQL source is clean. What's SQL Server data type? – Zane Apr 23 '14 at 12:57
  • I would dump that column to txt and search for any character codes that do not match a char() code in the target range. Also, your use of screenshots and error codes is exemplary. – Eric Hauenstein Apr 23 '14 at 13:00
  • @Zane I checked again, the MySQL data source is clean. Values are from 0.82 to 3475 and the data type is double. About SQL Server Data type it's DT_R8 for the output in SSIS and the table's column is of type FLOAT NOT NULL. – K4timini Apr 23 '14 at 13:14
  • 1
    This issue seems clearly to be with the Oracle Destination. I would change the Error Output setting for that object to Redirect Row, then add a Multicast downstream and add a debug Data Viewer on the output path. This should trap the rows with errors. – Mike Honey Apr 24 '14 at 04:22
  • @MikeHoney I changed to redirect errors to a Excel destination file. The error code is equal to 1, the error column is always equal to zero. Any idea ? – K4timini Apr 24 '14 at 08:33
  • @MikeHoney It seems this error is due to a specific separator for decimal values. In France we use a comma, American use a dot to separate decimal values. – K4timini Apr 24 '14 at 09:09

1 Answers1

2

Your NLS environment does not match the expected one. Default, Oracle assumes that "," is the grouping character and "." is the decimal separator. Make sure that your session uses the correct value for the NLS_NUMERIC_CHARACTERS parameter.

See Setting Up a Globalization Support Environment for docu.

  • I tried to configure le NLS environment without any success. Finally I changed the Oracle Destination provided by Attunity to the default OLE DB Destination and it works find ! – K4timini Apr 24 '14 at 13:12