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
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

Question has a verified solution.

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

This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Viewers will learn how the fundamental information of how to create a table.

623 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