Errors using SSIS (SQL Server Integration Services) 2005 to extract records from an IBM AS400 to an SQL table (workaround)

Written by: Greg Van Mullem

Date: February 11, 2006

This web page is part of a collection of web pages containing difficult to find technical information about the initial release of Microsoft SQL Server 2005 Standard Edition. Click here to go to the main index.

I got some strange errors using SSIS to extract (ETL) data from our IBM AS400 to SQL 2005 tables. Judging by the number of forum questions I saw about this it must be a common problem. I’m not sure what is causing these errors but I did find a good workaround. I can’t take full credit for this workaround. I found a forum post that didn’t work but it did put me on the right track.

These errors occurred when I was using the “OLE DB Source” and/or “SQL Server Destination” components. The workaround is to use the “Data Reader Source” and “OLE DB Destination” components. I had to configure my data source to use the “.Net Providers\Odbc Data Provider” provider. I’m also using a Control Panel System DSN with my data source. Our AS400 requires some non-default settings and I don’t know how to build a connection string for them. I tried a lot of other combinations of components that did not work! There may be other combinations that do work.

I installed the AS400 ODBC drivers on our SQL Server and my Windows XP workstation (where I’m designing my SSIS packages). I also put the Control Panel System DSN settings on both computers.

The really odd thing is that the preview button in the edit dialog box of “OLE DB Source” works just fine but the component fails when I run the package.

Here are the error messages I got:

[OLE DB Source [1]] Error: An OLE DB error has occurred. Error code: 0x80040E00.

[DTS.Pipeline] Error: The PrimeOutput method on component "OLE DB Source" (1) returned error code 0xC0202009.  The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing.