|
CraYon
Starting Member
1 Posts |
 Posted - 05/17/2012 : 02:48:39
|
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.
[url=http://imageshack.us/photo/my-images/204/errorrmi.jpg/]http://imageshack.us/photo/my-images/204/errorrmi.jpg/[/url]
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
|
|
Loading data to Oracle using SSIS
