AS/400 Stored Procedures in SSIS

Article Index
AS/400 Stored Procedures in SSIS
Page 2
Page 3
All Pages

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:

Image

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:

 

  1. using System;     
  2. using System.Collections.Generic;     
  3. using System.Text;     
  4. using System.Data;     
  5. using System.Data.OleDb;     
  6.      
  7. namespace AS400   
  8. {     
  9.   public class API     
  10.   {         
  11.     private string sConnectionString;         
  12.     private OleDbConnection con = new OleDbConnection();         
  13.      
  14.     // Default: Development environment     
  15.     public API()     
  16.     {     
  17.       sConnectionString = "Provider=IBMDA400.DataSource.1;Data Source=SERVERD;Persist Security Info=True;Password=*****;User ID=MFLXODBC;Initial Catalog=SERVERD;Catalog Library List=MFLXDATA";     
  18.       con.ConnectionString = sConnectionString;     
  19.       con.Open();     
  20.     }     
  21.     // SERVERD, MFLXODBC, FIGBUSH07, MFLXDATA     
  22.     public API(string sServer, string sUser, string sPasswd, string sCatalog)     
  23.     {     
  24.       // Make up connection string     
  25.       sConnectionString = "Provider=IBMDA400.DataSource.1;Data Source="+sServer+";Persist Security Info=True;Password="+sPasswd+";User ID="+sUser+";Initial Catalog="+sServer+";Catalog Library List=" + sCatalog;     
  26.       con.ConnectionString = sConnectionString;     
  27.       con.Open();     
  28.     }     
  29. . . .

 

 

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. 

  1. public void StoredProc( 
  2.       ref string sReturnCode         // OK if success, else AS400 return code 
  3.       ) 
  4.     { 
  5.       OleDbCommand cmd=new OleDbCommand()
  6.       cmd.Connection=con; 
  7.       cmd.CommandType=System.Data.CommandType.StoredProcedure
  8.    
  9.       // In the form:  LibraryName.ProcedureName 
  10.       cmd.CommandText = "ACSLPGM.ALEQXFK"
  11.       try 
  12.       { 
  13.         // Executes the equivalent of "call ACSLPGM.ALEQXFK('')" 
  14.         cmd.Parameters.Add("@RETURNCODE", OleDbType.Char, 7)
  15.         cmd.Parameters["@RETURNCODE"].Value = ""
  16.         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:

  1. . . .
  2.        cmd.ExecuteNonQuery();   
  3.         try   
  4.         {   
  5.           // Get the return parameters   
  6.           sReturnCode = cmd.Parameters["@RETURNCODE"].Value.ToString();   
  7.         }   
  8.         catch (Exception ex1)   
  9.         {   
  10.           sReturnMessage = ex1.Message.ToString();             
  11.         }   
  12.       }   
  13.       catch (Exception ex2)   
  14.       {   
  15.         sReturnMessage = ex2.Message.ToString();             
  16.       }   
  17.     } // ...StoredProc   
  18.   } // ...class API   
  19. } // ...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:

(TODO) 

 

Build and deploy the C# Assembly to the GAC

 

Ctrl-Shift-B. Simple. 


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)

 

  1. Imports System 
  2. Imports System.Data 
  3. Imports System.Math 
  4. Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper 
  5. Imports Microsoft.SqlServer.Dts.Runtime.Wrapper 
  6. Imports AS400 
  7.    
  8. Public Class ScriptMain 
  9.     Inherits UserComponent 
  10.    
  11.     Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer) 
  12.         Dim server As String = Me.Variables.AS400Server 
  13.         Dim userid As String = Me.Variables.AS400UserId 
  14.         Dim passwd As String = Me.Variables.AS400Passwd 
  15.         Dim catalog As String = Me.Variables.AS400Library 
  16.         Dim a As New API(server, userid, passwd, catalog) 
  17.    
  18.         Dim sRetCode As String = "" 
  19.         Dim iResult As Integer = a.StoredProcedure(sRetCode) 
  20.         If iResult <> 0 Then 
  21.             Me.Log("Failed - Error: " + sRetMsg, 1, Nothing) 
  22.         End If 
  23.     End Sub 
  24.    
  25. End Class

 

Further Enhancements

 

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.

 

Performance 

 

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?