AS/400 Stored Procedures in SSIS

Article Index

Page 3 of 3

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?