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
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?