none
moving data from SSIS to As400 using IBM DB2 UDB OLEDB provider, failing due to special symbol – endash ANSI value 0150

    Question

  • Hi,

    I am getting error while moving data from SSIS dataflow task using provider "IBM DB2 UDB for iseries IBMDASQL OLEDB provider" , it was running fine,suddenly starts failing I noticed that the rows that are failing contains symbols " –  " called as endash having ANSI code 0150. This is stored in varchar field in SQL Server 2008R2, and is mapped to character data type column of AS400/DB2 database. but its failing with error "CWBZZ5014 Value of parameter columnName could not be converted to the host data type."."CWBNL0107 - Converted 80 bytes, 2 errors found beginning at offset 10 ".

    AS I found that this special symbol is at 11th position in the given column.

    I have tried changing force translation value from 65535 to 1252 (Win Latin-1) but its not working ?

    Can someone help here.

    Thanks,

    Monday, April 08, 2013 9:00 AM

Answers

All replies

  • Hi,

    Either change your OS/400 destination table to Unicode fields (code page 65001).

    Or alter your SSIS by adding a derived column, which would be equal to the offending column with a replace of the 11th character (ANSI 0150 by ASCII 43,

    the "minus" sign).

    NewColumn = SUBSTRING(OldColumn, 1, 10) + "-" + SUBSTRING(OldColumn, 12, LEN(OldColumn) - 11)


    Sebastian Sajaroff Senior DBA Pharmacies Jean Coutu

    Monday, April 08, 2013 12:17 PM
  • Thanks Sajaroff,

    Can you please tell how to implement first method.

    Monday, April 08, 2013 12:50 PM
  • Hi,

    Let's suppose you want to copy a table with an Unicode field from SQL to DB2/400.

    On SQL Server, your origin table may look like :

    CREATE TABLE Items

    (MyID INT NOT NULL,

    MyDescription NVARCHAR(50),

    CONSTRAINT PK_Inventory PRIMARY KEY CLUSTERED (MyID ASC))

    On the OS/400 server, it should be defined as follows

    CREATE TABLE Items                

    (MyID INT NOT NULL,
     MyDescription VARGRAPHIC(100) CCSID 13488,
     PRIMARY KEY(MyID))

    Note : DB2/400 expects string fields size to be defined in bytes, so 100 bytes are required to store the 50 Unicode characters coming from SQL Server.

    Finally, on the DB2/400 connection check that the "Force Translate" property is set to 65535.

    Sebastian Sajaroff Senior DBA Pharmacies Jean Coutu

    Monday, April 08, 2013 1:11 PM
  • Let me try this and I'll update you.

    Thanks,

    RK

    Tuesday, April 09, 2013 5:26 AM
  • it worked, but the data that is stored in AS400 is not readable. its in the form of some symbols.
    Tuesday, April 09, 2013 6:41 AM
  • Hi,

    How are you reading the data once arrived to AS/400?

    An RPG program? iSeries Navigator? An ODBC query?


    Sebastian Sajaroff Senior DBA Pharmacies Jean Coutu

    Tuesday, April 09, 2013 12:03 PM
  •  we are using iSeries Navigator..though we are getting correct result in SQL Server thru linked server, but not displayed accuratly using navigaoter.

    Tuesday, April 09, 2013 12:19 PM
  • Hi,

    As far as I know, you have two choices to fix your iSeries Navigator issue :

    1. Either change your OS/400 default CCSID to 13488

    2. Or use VARCHAR function to convert your VARGRAPHIC field to 8-bits text.

    SELECT VARCHAR(your_vargraphic_field, max_length) FROM your_library.your_table


    Sebastian Sajaroff Senior DBA Pharmacies Jean Coutu

    Tuesday, April 09, 2013 1:03 PM
  • Thanks, for looking into this. We'll let the client know the alternatives.

    • Marked as answer by R_a_j_e_s_h Wednesday, April 10, 2013 4:23 AM
    Wednesday, April 10, 2013 4:23 AM
  • Thanks, for looking into this.
    • Marked as answer by R_a_j_e_s_h Wednesday, April 10, 2013 4:26 AM
    Wednesday, April 10, 2013 4:26 AM
  • Hi,

    Note : 1. Either change your OS/400 database user default CCSID to 13488.

    You don't have to change the CCSID across the whole server, only for the user that actually connects to the database and queries those Unicode fields


    Sebastian Sajaroff Senior DBA Pharmacies Jean Coutu

    Wednesday, April 10, 2013 12:10 PM