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

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
Who is Participating?
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.

Gary PattersonVP Technology / Senior Consultant Commented:
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
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
Try to connect to your database using MS query, for example via excel. Use the same definitions and see what happens.
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.