E_Fail Status when previewing data in SSIS Data Source (DB2 AS400)

Posted on 2011-09-28
Last Modified: 2013-11-10
I am using the Microsoft OLE DB Provider for DB2 driver to connect to a DB2 AS400 system.  I have been using the driver just fine without a problem for years, but today I needed to pull data from a different AS400 file that has the ability to store Double Byte Character Set (DBCS) columns.  As far as I know, that is the only difference between the files that work and the ones that don't.   I set everything in my SSIS connection and Data Flow task up as normal, select the table from the dropdown list in the OLE DB Source Editor and hit the preview button to see the data, but am greeted with "Data provider or other services returned an E_FAIL status".  After a day of working with this I've determined that is just a generic error that doesn't provide any info...just the driver failed to return data.

The problem appears to be that the OLE driver doesn't know how to deal with the CCSID of the columns I am trying to import.  If I write a query and explicitly cast the column to a different CCSID it works fine.  

For example this works:  SELECT CAST(ColumnName AS CHAR(5) CCSID 37)
This doesn't:  SELECT ColumnName

While that sounds like a solution in itself, it's obnoxious to have to write a query for every data source and convert every single column explicitly.  Some of these tables have over 100 columns!  I've tried setting the AlwaysUseDefaultCodePage to True, but that didn't help.  I even tried changing the code page to the host code page and that didn't work.  Surely there has to be a simple solution, but I just can't find it!

Driver settings:
The Host CCSID is 37.
PC Code Page is 1252.
The column in question has a CCSID of 28709 as indicated by iSeries Navigator. There other columns with different CCSID's but if I can just get this one to work I'd be happy.  Any suggestions would be greatly appreciated!
Question by:Swindle
  • 2
  • 2
LVL 27

Expert Comment

ID: 36818817
Driver settings:
The Host CCSID is 37.

The driver settings apparently say what the driver should expect from the server. Do you know what the server itself is set as?

What is the QCCSID system value? A site that is DBCS aware should probably know how to set their system CCSID appropriately, so I don't especially expect that system value to be inappropriate. But it might suggest a direction.

Can you send a command through your database connection? (That is, are you authorized to execute commands through the connection?) Depending on the server system setting, you might send a command that sets the CCSID on the server side to 37. Ideally, that's what I'd want to happen automatically from a "Host CCSID" driver setting, but I don't know what OLEDB actually does with it. I might call the system QCMDEXC API to execute a CHGJOB command for CCSID(37).

None of that is a proposed fix. Mostly it's just to learn more about exactly where the problem is.

LVL 11

Accepted Solution

Swindle earned 0 total points
ID: 36861765
Thanks for the reply Tom.  The Host is set to a default CCSID of 37.  That's why we've been able to work successfully without encountering this problem for years.  The collection/file in AS400 that we started pulling from contains columns that are not available in that Code Page.  After much effort I found the resolution.  

If you are not using Unicode code pages, the data provider will only be able to read the data from the columns that are using similar code pages as defined on the connection string. Code pages 37/1252 are Latin code pages (English/European).  28709 is Traditional Chinese EBCDIC (Extended).  CCSID 37 is LATIN EBCDIC. It does not contain the DBCS characters in order to translate the Chinese characters. 1252 won't work either, as that is ANSI Latin characters, not Chinese. To get this column and the rest of the file to translate I had to change the settings in the OLE Connection to CCSID=937;PC Code Page=950.  The preview now works without having to cast the columns.  Unfortunately for anyone else that sees this problem in the future those settings probably won't be the same.  It will take a different combination of CCSID and PC Code Page that matches the CCSID of the columns contained within your AS400 file.
LVL 11

Author Closing Comment

ID: 36908504
After 24 hours of trying to resolve the issue with no comments I figured out the solution on my own.
LVL 27

Expert Comment

ID: 36883949
...a default CCSID of 37.

Did you intend that to mean "default"? Or is that a reference to the actual QCCSID system value?

I have no objection to closing the question. I responded only to get some info flowing. Glad it worked out for you.


Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
getting error Subquery returned more  than 1 value 6 21
Database maintenance 36 98
SQL Query Task 11 42
Data encryption options between SQL DBs 3 21
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
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, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

685 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