Loading data to Oracle using SSIS

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

SSIS and Import/Export (2008)

Loading data to Oracle using SSIS

Author

Topic

CraYon
Starting Member

1 Post

Posted - 2012-05-17 : 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

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2012-05-22 : 15:04:08

easiest approach would be

1. dump the oracle data to xml
2. ingest the xml dump via ssis
3. Move xml file to PROCESSED folder or delete it

<><><><><><><><><><><><><><><><><>
If you don't have the passion to help people, you have no passion

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources