E_Fail Status when previewing data in SSIS Data Source (DB2 AS400)
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!
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!
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
After 24 hours of trying to resolve the issue with no comments I figured out the solution on my own.
...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.
Tom
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.
Tom
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.
Tom