SSIS 2005. Fast load into non-MS OLE DB destination

or

SSIS 2005. How to avoid Autocommit when load into OLE DB destination.

An example with using of Oracle OLE DB driver on a destination side.

I have heard from my friend that he is experiencing very slow load into Oracle database. He used SSIS 2005. Which is funny, because Oracle Warehouse Builder is available. Anyway.
The cause of slowness of SSIS, when it loads into non-Microsoft OLE DB target, appeared to be the commit after every insert. Since the ‘Autocommit’ is set to be ‘ON’ by OLE DB standard, a driver propagates this mode to the database. The only way to turn it off, is to switch OLE DB datasource into a Transactional mode by issuing a BeginTransaction() call.

SSIS does issue this call when Batch Size option is specified for MS SQL OLE DB Native Sqlserver driver. But it does not do it for any other OLE DB driver like Oracle’s or even for MSSQL .Net OLE DB.

As a result, SSIS demonstrates very poor performance when loading into Oracle OLEDB Destination.

What to do?

I’ve suggested to start transaction in Destination OLE DB session before any insert comes, then do a Commit and start transaction again after some number of rows inserted in order to simulate “Batch size”.

How to do?

I would be curious to know if anybody have found a more elegant and efficient way to fix this situation, however my way is by using Script Component.

1. Create Any kind of Data Source, i.e. an OLE DB Source. It should have fields that can be easily put into a target table.
2. Add Script Component as a Destination and feed it from data source above.
3. Double-click on it to open Script Transformation Editor
   Select input columns

Select Connection Manager form where add New connection manager.

Rename new “Connection” to “oraxeConnection” just for distinction.

In Connection Manager Column choose “New Connection” item

Select “ADO.NET” type

In a dialog “Configure OLE DB Connection Manager” click New.

Choose Provider. In my simple case “.Net Providers for OleDb\Microsoft OLE DB Provider for SQL Server”

Test and click OK

Click OK

Choose newly created Connection Manager in “Connection Manager” column

Select “Script” item in left list

Click [Design Script…] button

Create following script

' Microsoft SQL Server Integration Services user script component
' This is your new script component in Microsoft Visual Basic .NET
' ScriptMain is the entrypoint class for script components

Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper
Imports Microsoft.SqlServer.Dts.Runtime.Wrapper
Imports System.Data.OleDb
Imports System.Data.Common

Public Class ScriptMain
Inherits UserComponent

Dim rowcnt As Int64
Dim out_batch_sz As Int64

Dim connMgr As IDTSConnectionManager90
Dim oledbconn As OleDbConnection
Dim oledbtran As OleDbTransaction
Dim oledbCmd As OleDbCommand
Dim oledbParam As OleDbParameter

Public Overrides Sub PreExecute()
out_batch_sz = 8 * 1024
rowcnt = 0
'MsgBox("PreExecute")

oledbCmd = New OleDbCommand("INSERT INTO MM_TST_B2(C, D) VALUES(?, ?)", oledbconn)
oledbParam = New OleDbParameter("@C", OleDbType.VarChar, 7)
oledbCmd.Parameters.Add(oledbParam)
oledbParam = New OleDbParameter("@D", OleDbType.VarChar, 7)
oledbCmd.Parameters.Add(oledbParam)

oledbtran = oledbconn.BeginTransaction()
oledbCmd.Transaction = oledbtran
'MsgBox("First Transaction began")
MyBase.PreExecute()
End Sub

Public Overrides Sub AcquireConnections(ByVal Transaction As Object)
connMgr = Me.Connections.oraxeConnection
oledbconn = CType(connMgr.AcquireConnection(Nothing), OleDb.OleDbConnection)
'MsgBox(oledbconn.Provider & " " & oledbconn.ConnectionString)
End Sub


Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)
With oledbCmd
.Parameters("@C").Value = Row.C
.Parameters("@D").Value = Row.D
.ExecuteNonQuery()
End With

rowcnt = rowcnt + 1
If (rowcnt Mod out_batch_sz) = 0 Then
MsgBox("Batch Commit " & rowcnt)
oledbtran.Commit()
oledbtran = oledbconn.BeginTransaction()
oledbCmd.Transaction = oledbtran
'MsgBox("Batch Transaction began")
End If
End Sub

Public Overrides Sub PostExecute()
'MsgBox("PostExecute")
MyBase.PostExecute()
End Sub

Public Overrides Sub ReleaseConnections()
'MsgBox("ReleaseConnections")
oledbtran.Commit()
MyBase.ReleaseConnections()
End Sub

Save and exit.
Run

Comparison of this Script Destination method with the basic OLE DB Destination, where both work via the same Oracle OLE DB Driver, shows that the Script method is times faster.