Loading data to Oracle using Script Component
Hi all,
I found problem loading data from MSSQL Server to Oracle and between Oracle about performance issue. I try to use Oracle attunity but it's not support in MSSQL standard edition, so I have to use script component as destination follow the suggestion because
no budget to use 3rd party component.
I found instuction about coding in script component and following the instruction. After I finish coding it complied successful but SSIS show the error below.
So please help to suggest about the code below.
Thank you in advance.
==============================================
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
<Microsoft.SqlServer.Dts.Pipeline.SSISScriptComponentEntryPointAttribute> _
<CLSCompliant(False)> _
Public Class ScriptMain
Inherits UserComponent
Dim row_count As Int64
Dim batch_size As Int64
Dim connMgr As IDTSConnectionManager100
Dim oledbconn As OleDbConnection
Dim oledbtran As OleDbTransaction
Dim oledbCmd As OleDbCommand
Dim oledbParam As OleDbParameter
Public Overrides Sub PreExecute()
MyBase.PreExecute()
batch_size = 8 * 1024
row_count = 0
oledbCmd = New OleDbCommand("INSERT INTO STGDW.STG1_TOPS_UMI_CUSTOMER_ARCHIVE(ACCOUNTNUMBER, CARDNUMBER) VALUES(?, ?)", oledbconn)
oledbParam = New OleDbParameter("@ACCOUNTNUMBER", OleDbType.Integer, 38)
oledbCmd.Parameters.Add(oledbParam)
oledbParam = New OleDbParameter("@CARDNUMBER", OleDbType.Integer, 38)
oledbCmd.Parameters.Add(oledbParam)
oledbtran = oledbconn.BeginTransaction()
oledbCmd.Transaction = oledbtran
MyBase.PreExecute()
End Sub
Public Overrides Sub AcquireConnections(ByVal Transaction As Object)
connMgr = Me.Connections.ORAConnection
oledbconn = CType(connMgr.AcquireConnection(Nothing), OleDb.OleDbConnection)
End Sub
Public Overrides Sub PostExecute()
MyBase.PostExecute()
End Sub
Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)
With oledbCmd
.Parameters("@ACCOUNTNUMBER").Value = Row.cvAccountNumber
.Parameters("@CARDNUMBER").Value = Row.cvCardNumber
.ExecuteNonQuery()
End With
row_count = row_count + 1
If (row_count Mod batch_size) = 0 Then
oledbtran.Commit()
oledbtran = oledbconn.BeginTransaction()
oledbCmd.Transaction = oledbtran
End If
End Sub
Public Overrides Sub CreateNewOutputRows()
End Sub
Public Overrides Sub ReleaseConnections()
oledbtran.Commit()
MyBase.ReleaseConnections()
End Sub
End Class
May 17th, 2012 2:40am
Your code is designed to process the records on a row by row basis thus may not (IMHO) be any faster than what the stock Oracle connector offers.
As to the code my cursory look tells me you are missing the
oledbtran = New OleDbTransaction Arthur My Blog
Free Windows Admin Tool Kit Click here and download it now
May 17th, 2012 10:06am
I think you're code is based on the example is this MSDN article (http://msdn.microsoft.com/en-us/library/hh923024.aspx ). We're updating the example to remove the oledbtran.Commit()
from ReleaseConnections(), as Chuck recommended.Carla Sabotta
September 4th, 2012 3:02pm