The calling of AS/400 Stored Procedures from SSIS has a few mysteries. Here is the solution that I eventually came up with - it consists of using typical OleDb objects and their usual gang of methods, all done in C#, with a smattering of VB script to glue the whole C# assembly into SSIS 2005.
Delving through the possible AS/400 drivers and miriad of approaches, combinations of the following problems arose:
Using an Execute SQL Task:
- It is not possible to call the stored procedure from an Execute SQL Task
- Getting the return parameters from AS/400 stored procedures is impossible
- Getting the the resulting recordset from an AS/400 stored procedure is impossible
Using a VB Script task/component:
- Not all AS/400 driver support calling of stored procedures
- If you can call the stored procedure, it is impossible to get the return parameters.
I have not yet found an AS/400-compatible driver that allows me to get the return parameters from a stored procedure if I call it from VB. I strongly suspect that the error lies with VB and not with the driver, based on my prejudice love of the VB programming language, and also because I managed to do all this and more using C#.
Choose the right AS/400 driver
IBM's iSeries ODBC driver only works for simple table extracts from SSIS. The driver which eventually worked is what is displayed in SSIS as "Native OLE DB\IBM DB2 UDB for iSeries IBMDA400 OLE DB Provider". This comes by default with SSIS (if I remember correctly).
Check basic connectivity of the driver and from your development environment by creating a Data Source to your AS/400 server. It should look something like this:
Hit the Test Connection button. Do not proceed until you have your basic connectivity configured.
Note the connection string, which should looke like this: "Provider=IBMDA400.DataSource.1;Data Source=SERVERD;Persist Security Info=True;Password=******;User ID=MFLXODBC;Initial Catalog=SERVERD;Catalog Library List=MFLXDATA". I use this string to crudely create an OleDbConnection later on.