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.
Create a C# Assembly
The key to getting all aspects of an AS/400 stored to work is to call it from a C# assembly. Since C# cannot be used directly from SSIS 2005 (this should change for SSIS 2008), a minimal amount of VB wrapper script needs to be written in SSIS to call the C' assembly. The process of creating a C# assembly in general is described in this article. Here, I specialize a little and show you how to call an AS/400 stored procedure.
Create a C# Class Library project called AS400. Rename the default class name is somethig useful like API. Create a keyfile (see here on how this is done) and add this to your project.
Create the connection object
Add the "using System.Data.OleDb;" declaration to your C# class. The connection object is instantiated in the constructor using the crude connection string approach. This is what the class looks like so far:
- using System;
- using System.Collections.Generic;
- using System.Text;
- using System.Data;
- using System.Data.OleDb;
- namespace AS400
- public class API
- private string sConnectionString;
- private OleDbConnection con = new OleDbConnection();
- // Default: Development environment
- public API()
- sConnectionString = "Provider=IBMDA400.DataSource.1;Data Source=SERVERD;Persist Security Info=True;Password=*****;User ID=MFLXODBC;Initial Catalog=SERVERD;Catalog Library List=MFLXDATA";
- con.ConnectionString = sConnectionString;
- // SERVERD, MFLXODBC, FIGBUSH07, MFLXDATA
- public API(string sServer, string sUser, string sPasswd, string sCatalog)
- // Make up connection string
- sConnectionString = "Provider=IBMDA400.DataSource.1;Data Source="+sServer+";Persist Security Info=True;Password="+sPasswd+";User ID="+sUser+";Initial Catalog="+sServer+";Catalog Library List=" + sCatalog;
- con.ConnectionString = sConnectionString;
- . . .
Declare parameters to the AS/400 Stored Procedure
For simplicity's sake, this stored procedure has only one INOUT parameter, which is passed back out of the public class method. Do the same for all the other parameters of your stored procedure and remember to set accurately state the data types and parameter direction.
- public void StoredProc(
- ref string sReturnCode // OK if success, else AS400 return code
- OleDbCommand cmd=new OleDbCommand();
- // In the form: LibraryName.ProcedureName
- cmd.CommandText = "ACSLPGM.ALEQXFK";
- // Executes the equivalent of "call ACSLPGM.ALEQXFK('')"
- cmd.Parameters.Add("@RETURNCODE", OleDbType.Char, 7);
- cmd.Parameters["@RETURNCODE"].Value = "";
- cmd.Parameters["@RETURNCODE"].Direction = ParameterDirection.InputOutput;
Execute the Stored Procedure and collect returned parameter values
Continuing with the code above, our stored procedure is executed using the OleDbCommand.ExecuteNonQuery() method:
- . . .
- // Get the return parameters
- sReturnCode = cmd.Parameters["@RETURNCODE"].Value.ToString();
- catch (Exception ex1)
- sReturnMessage = ex1.Message.ToString();
- catch (Exception ex2)
- sReturnMessage = ex2.Message.ToString();
- } // ...StoredProc
- } // ...class API
- } // ...namespace AS400
Collecting recordset data from a stored procedure
If the store procedure returns a record set, then you would call it differently and collect the resulting data and collect the data as follows:
Build and deploy the C# Assembly to the GAC
Using the C# Assembly in SSIS
This process is also described in more detail in this article this article.
Parameterising the AS/400 connection in SSIS
Good practise suggests that the connection is parameterised, so we pass the following SSIS variables to the Script Task/Component (Remember: No spaces between them!): AS400Library,AS400Passwd,AS400Server,AS400UserId.
For some odd reason, the SSIS variables need to be collected into VB variables first before they can be used (I told you that VB is bollocks).
Executing the Stored Procedure
The stored procedure is invoked from a VB Script Component or VB Script Task shown below. (It is possible to pass the SP's returned parameter back out through the Row variable if a suitable output parameter were specified for the Script component. This is trivial and therefore not shown)
- Imports System
- Imports System.Data
- Imports System.Math
- Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper
- Imports Microsoft.SqlServer.Dts.Runtime.Wrapper
- Imports AS400
- Public Class ScriptMain
- Inherits UserComponent
- Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)
- Dim server As String = Me.Variables.AS400Server
- Dim userid As String = Me.Variables.AS400UserId
- Dim passwd As String = Me.Variables.AS400Passwd
- Dim catalog As String = Me.Variables.AS400Library
- Dim a As New API(server, userid, passwd, catalog)
- Dim sRetCode As String = ""
- Dim iResult As Integer = a.StoredProcedure(sRetCode)
- If iResult <> 0 Then
- Me.Log("Failed - Error: " + sRetMsg, 1, Nothing)
- End If
- End Sub
- End Class
It is possible to reuse an existing Data Source from SSIS. The connection details can be accessed using VB script and these can then be passed to the eventual C# OleDbConnection object instead of using the SSIS user variables as shown in the example above.
When used in a Script component, the OleDBbConnection object is created and destroyed for every record processed, implying that a connection to the AS/400 is established every time. The performance of this approach is not great, but I am unsure where the bottleneck is - it may that this OleDb driver does not use connection pooling?