Expiring Today—Celebrate National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17


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

Posted on 2011-09-28
Medium Priority
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

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Question has a verified solution.

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

It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

730 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