Execute PLSQL Code in an Oracle Package from SSIS

Problem

We have a number of Oracle stored procedures that we need to execute from an SSIS package.  The PL/SQL code is deployed in various Oracle packages.  Many of these stored procedures require parameters and we’re struggling to find the best way to do this given that the various Oracle connection manager types don’t support parameterized queries.  Do you have any ideas?

Solution

The lack of support for parameterized queries in the various Oracle connection managers is an unfortunate limitation in SSIS.  However, as is usually the case, there has to be an alternative that will get the job done.  The solution that I prefer is to use the Script Component in the Data Flow to execute the Oracle PL/SQL code.  The advantage of this approach is that you can parameterize the PL/SQL code as well as take care of other things like setting the transaction isolation level, managing transactions, and handling exceptions.  This is done by implementing what’s called an anonymous PL/SQL block.

Let’s walk through the steps involved to implement this.  To add a little bit of context to this example, the requirement was to extract certain student loan records, transform them into a standard format used by educational institutions (called CommonLine4 or CL4), and send the CL4 file to a disbursing agent who transfers the funds to the school on the student’s behalf.  While you may not think of SSIS right away for this task, it worked well.

Start out with a new SSIS package, right click in the Connection Managers area and select Add New ADO.NET Connection.  Select the Provider as shown; this is the Microsoft .NET Data Provider for Oracle.  The Server name is the Oracle instance name.

The stored procedure we need to call takes a single input parameter – the number of days before the disbursement date; i.e. we want to extract the loans that have a scheduled disbursement within the next so many days.  Setup a package variable as follows:

For demonstration purposes the value is simply hard-coded.  However, check out our previous tips on package configuration using SQL Server or XML files.

The next step is to add a Data Flow task, then add a Script Component to the Data Flow.  When prompted for the Script Component Type select Source.

There are three steps to be performed on the Script Transformation Editor that is displayed after selecting the Script Component Type.  Change Output 0 to OutputBuffer on the Inputs and Outputs  page.

Add the Oracle connection manager that we defined earlier to the Connection Managers page.  This is an important step; it allows us to use the connection manager in the VB.NET code but SSIS takes care of managing the connection.

Return to the Script page in the Script Transformation Editor and click the Design Script button to enter the VB.NET code as shown below:

Imports System

Imports System.Data

Imports System.Math

Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper

Imports Microsoft.SqlServer.Dts.Runtime.Wrapper

”’ ADD IMPORT

Imports System.Data.OracleClient

Public Class ScriptMain

    Inherits UserComponent

”’ DECLARATIONS

    Private connMgr As IDTSConnectionManager90

    Private oracleConn As OracleConnection

    Private oracleCmd As OracleCommand

”’ GET CONNECTION

    Public Overrides Sub AcquireConnections(ByVal Transaction As Object)

      connMgr = Me.Connections.ORCL

      oracleConn = CType(connMgr.AcquireConnection(Transaction), OracleConnection)

    End Sub

”’ SETUP PL/SQL BLOCK

    Public Overrides Sub PreExecute()

        Dim plsql As String = 

          “BEGIN ” + _

          ”    SET TRANSACTION ISOLATION LEVEL READ COMMITTED; ” + _

          ”    CL4_pkg.InitExtract (:DaysToDisbursement); ” + _

          ”    COMMIT; ” + _

          “EXCEPTION ” + _

          ”    WHEN OTHERS THEN ” + _

          ”        ROLLBACK; ” + _

          ”        RAISE; ” + _

          “END;”

        oracleCmd = New OracleCommand(plsql, oracleConn)

        With oracleCmd

            .CommandType = CommandType.Text

            .Parameters.Add(“:DaysToDisbursement”, OracleType.Number)

        End With

    End Sub

”’ DISPOSE

    Public Overrides Sub PostExecute()

        If Not oracleCmd Is Nothing Then

            oracleCmd.Dispose()

        End If

    End Sub

”’ RELEASE

    Public Overrides Sub ReleaseConnections()

        connMgr.ReleaseConnection(oracleConn)

    End Sub

”’ EXECUTE PL/SQL CODE

    Public Overrides Sub CreateNewOutputRows()

        Try

            OutputBuffer.AddRow()

            Dim days = Me.Variables.vDaysToDisbursement

            With oracleCmd

                .Parameters(“:DaysToDisbursement”).Value = days

                .ExecuteNonQuery()

            End With

        Catch ex As Exception

            Me.ComponentMetaData.FireError(-1, “InitExtract”,

               ex.Message, String.Empty, 0, True)

        End Try

        OutputBuffer.SetEndOfRowset()

    End Sub

End Class

Let’s wrap up with a brief explanation of each comment in the above block of VB.NET code:

• ADD IMPORT: add the Oracle client namespace; this is the namespace where the Oracle ADO.NET objects are found in the .NET framework.
• DECLARATIONS: declare the SSIS connection manager and the Oracle ADO.NET objects.
• GET CONNECTION: get the Oracle connection from the SSIS connection manager.
• SETUP PL/SQL BLOCK: this is where the anonymous PL/SQL block is defined.  Note that we can parameterize the statement, set the transaction isolation level, and catch any exceptions.
• DISPOSE: dispose of the connection.
• RELEASE: tell the connection manager we are done with the connection.
• EXECUTE PL/SQL CODE: Execute the anonymous PL/SQL block.

While there is quite a bit of code shown in the Script Component, most of it is just boilerplate stuff; only a small amount of the code is different in each instance of the Script Component.

Next Steps

• If you need to execute Oracle code and pass in a bunch of parameters, you might give this approach a try.  The alternative of concatenating the parameters together into a string and using an Execute SQL task to execute the command from a string isn’t necessarily bad, but if you have several parameters it can be tedious and error-prone.
• An enhancement to the above approach could be to deploy the code into a DLL, exposing a single function that could be called from the Script Component.