AS/400 Stored Procedures in SSIS

Article Index

Page 2 of 3

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.