locked
Oracle to Flat File Problem - Error: Data conversion failed. The data conversion for column "" returned status value 4 and status text "Text was truncated or one or more characters had no match in the target code page.". RRS feed

  • Question

  • All,

    This issue is driving me nuts.

    I'm trying to pull data from an Oracle database to a flat file and I'm having this issue that it seems to be related to the datatype between Oracle and SSIS.

    I checked the datatypes between the input and output columns on the Oracle source.

    I did the same on the flat file destination and it is all set to WSTR 90.

    The data coming from Oracle is set as VARCHAR2(90).

    I'm using the OraOLEDB.Oracle.1 provider.

    The error message I get:

    Error: Data conversion failed. The data conversion for column "PARTY_NAME" returned status value 4 and status text "Text was truncated or one or more characters had no match in the target code page.".

     

    Any ideas of what I could be doing wrong?


    --- Best Regards, Igor Santos - DBA igor.santos@citrix.com
    Tuesday, March 1, 2011 7:13 PM

Answers

  • That't what I wanted to ask. You need Unicode all the way.

    Arthur My Blog
    By: TwitterButtons.com
    • Edited by ArthurZ Tuesday, March 1, 2011 9:47 PM for a typo
    • Marked as answer by Jerry Nee Saturday, March 12, 2011 8:27 AM
    Tuesday, March 1, 2011 9:46 PM

All replies

  • Is it possible that any of your data columns coming from Oracle might contain a character you are using as a delimiter in your flat file?   or perhaps the data contains some extended character that isn't allowed in your flat file?
    -Tab Alleman
    Tuesday, March 1, 2011 7:37 PM
  • Try using DT_STR instead.

    Arthur My Blog
    By: TwitterButtons.com
    Tuesday, March 1, 2011 7:49 PM
  • Tab,

    Yes. The columns have all sorts of charaters, commas, collens, dots...

    I'm now trying to load the four problamatic fields into NVARCHAR(MAX) fields in a test database and I'm still getting the same issues.

    I also tried NTEXT and I get the same issue.

     

    Arthur,

    The Oracle source component shows a red x when I change the datatype for any columns, as it doesn't match with the data type in the source.

    When I move my mouse over, I see a warning saying:

    "Column PARTY_NAME cannot convert between unicode and non-unicode string data types"


    --- Best Regards, Igor Santos - DBA igor.santos@citrix.com
    Tuesday, March 1, 2011 8:18 PM
  • Put a data conversion in between and retry. Convert from Unicode to dt_str

    Arthur My Blog
    By: TwitterButtons.com
    Tuesday, March 1, 2011 8:41 PM
  • Tried that as well and didn't work.

    It failed at the conversion component.

    The conversion returned status value 4 and status text "Text was truncated or one or more characters had no match in the target code page.".

    The problem is that I'm loading data from different regions from Oracle to SQL.

    I see these japaneze and other characters on the data coming from Oracle and that's when the package fails.


    --- Best Regards, Igor Santos - DBA igor.santos@citrix.com
    Tuesday, March 1, 2011 9:19 PM
  • That't what I wanted to ask. You need Unicode all the way.

    Arthur My Blog
    By: TwitterButtons.com
    • Edited by ArthurZ Tuesday, March 1, 2011 9:47 PM for a typo
    • Marked as answer by Jerry Nee Saturday, March 12, 2011 8:27 AM
    Tuesday, March 1, 2011 9:46 PM