trying to Write to AS400 using SSIS Packs but getting error SQL 0189

Posted on 2011-05-06
Last Modified: 2016-10-19
Setup an SSIS pack using ISERIES IBMDA400 OLE DB and have the 'Force Translate' set to 65535. When I run the pack it tells me the CCSID 285 is not valid. 285 is the  correct lang id on the as400 and ther is no bad data that is being pulled across. For me it looks like the problem reside on the Microsoft SQL 2008 end but I am now at a loss as to what the problem may be. All assiatnace greatly welcomed
Question by:ocallco
    LVL 34

    Assisted Solution

    by:Gary Patterson
    Here's the full text for that error message - it has some clues.  Really need to see the SQL statement and connection string to offer much advice, and it would be handy to see DSPFFD output for each table involved int he failing query.

    Message Text:      Coded Character Set Identifier &1 not valid.
    Cause Text:      Coded Character Set Identifier (CCSID) &1 is not valid for one of the following reasons:

        The CCSID is not EBCDIC.
        The CCSID is not supported by the system.
        The CCSID is not valid for the data type.
        If the CCSID is specified for graphic or DBCLOB data, then the CCSID must be a DBCS CCSID.
        If the CCSID is specified for UCS-2 data, then the CCSID must be a UCS-2 CCSID.
        If the CCSID is specified for CLOB, DBCLOB or DATALINK data, then the CCSID must not be 65535.
        If there are multiple DATALINK columns with FILE LINK CONTROL, they must all have the same CCSID.
        A UTF-8 CCSID cannot be specified for the DECLARE VARIABLE statement.

    Recovery Text:      Ensure that all CCSID values in the statement are supported by the system and are valid for the data type. For a list of valid CCSID values, refer to the Work Management guide.

    - Gary Patterson
    LVL 22

    Expert Comment

    You will need to provide the SQL statement.

    it looks like the problem reside on the Microsoft SQL 2008

    By definition, since you are using SSIS, the problem is on the SQL Server side of the process; however, that doesn't mean that SSIS or SQL Server is flawed.  It may mean that the code is actaully not quite right.

    Reference the fpollowing:
    DB2 Code Page Fun
    LVL 26

    Accepted Solution

    The problem would seem to be due to having 'Force Translate' set to 65535. CCSID 65535 is a binary CCSID, by definition no translation should be done.

    Translations often can be done into many other CCSIDs by way of the system making assumptions about what the 65535 bits could be interpreted as meaning, by looking at the system's default language settings, etc. In this case, however, it would seem that at least some of the data has bit patterns (or data types) that make no sense to translate into CCSID 285. CCSID 285 should be the older UK English character set (before the Euro).

    But that's just some general background. The output from DSPFFD for tables that are accessed should help in pointing to the conversion obstacle. Normally, transfers of business data shouldn't need 'Force translate' for 65535. That CCSID shouldn't be used for data that contains translatable characters -- the actual CCSID of the data should be assigned. That eliminates the ambiguities and potential information loss (or corruption).

    It might be possible to run the transfer under the expected CCSID of the source data.

    LVL 32

    Expert Comment

    Try to connect to your database using MS query, for example via excel. Use the same definitions and see what happens.
    LVL 14

    Expert Comment

    This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.

    Featured Post

    Free Trending Threat Insights Every Day

    Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

    Join & Write a Comment

    Sometimes a user will call me frantically, explaining that something has gone wrong and they have tried everything (read - they have messed it up more and now need someone to clean up) and it still does no good, can I help them?!  Usually the standa…
    International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
    This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
    Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

    732 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    17 Experts available now in Live!

    Get 1:1 Help Now