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

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics