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

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

Suggested Solutions

Nowadays, some of developer are too much worried about data. Who is using data, who is updating it etc. etc. Because, data is more costlier in term of money and information. So security of data is focusing concern in days. Lets' understand the Au…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Viewers will learn how the fundamental information of how to create a table.

757 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

16 Experts available now in Live!

Get 1:1 Help Now