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!
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!