Execute PLSQL Code in an Oracle Package from SSIS
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?
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:
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 package variable that we created earlier to the ReadOnlyVariables list on the Script page. This allows us to access the variable in the VB.NET code. You can specify multiple variables if necessary; just separate them with a comma.
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.
- 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.
About the author
View all my tips
Article Last Updated: 2008-03-05