最近项目中老是需要从SQL Server数据库，把数据加载至Oracle数据库中。用SSIS自带的OLE DB Destination数据抽取加载的性能相当相当的慢，在我印象中曾经抽取100W左右的数据，抽取了将近9个小时。
提高数据加载至Oracle数据库性能， 我目前用过的有两种方法。第一种是是用SIS Connectors v2.0 for Oracle, 第二种是用Script Component. 相比而言。第一种简单方便，安装完一个SSIS插件后，就可以用新的Oracle Destination进行数据抽取加载。我在用这个组件时,比较麻烦点的地方就是做数据转换。可以从微软官方下载：
Download the SSIS Connectors v2.0 for Oracle and Teradata that provide additional value for SQL Server Integration Services (SSIS) 2012: https://www.microsoft.com/en-us/download/details.aspx?id=29283
微软官方文档关于此插件的介绍，我只找到SSIS 2008版本的: https://technet.microsoft.com/en-us/library/ee470675(v=sql.100).aspx
第二种的话我是看到一篇文档做的。大家可以从以下链接找此文档(搜:Optimized Bulk Loading of Data into Oracle)：
Large collection of Free Microsoft eBooks for you, including: SharePoint, Visual Studio, Windows Phone, Windows 8, Office 365, Office 2010, SQL Server 2012, Azure, and more：
http://blogs.msdn.com/b/mssmallbiz/archive/2012/07/27/large-collection-of-free-microsoft-ebooks-for-you-including-sharepoint-visual-studio-windows-phone-windows-8-office-365-office-2010-sql-server-2012-azure-and-more.aspx
附上VB Sample Code:
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()
batch_size = 8 * 1024
row_count = 0
oledbCmd = New OleDbCommand("INSERT INTO Person(BusinessEntityID,FirstName,MiddleName,LastName)VALUES(?,?,?,?)", oledbconn)
oledbParam = New OleDbParameter("@BusinessEntityID", OleDbType.Integer)
oledbCmd.Parameters.Add(oledbParam)
oledbParam = New OleDbParameter("@FirstName", OleDbType.VarChar)
oledbCmd.Parameters.Add(oledbParam)
oledbParam = New OleDbParameter("@MiddleName", OleDbType.VarChar)
oledbCmd.Parameters.Add(oledbParam)
oledbParam = New OleDbParameter("@LastName", OleDbType.VarChar)
oledbCmd.Parameters.Add(oledbParam)
oledbtran = oledbconn.BeginTransaction()
oledbCmd.Transaction = oledbtran
MyBase.PreExecute()
End Sub
Public Overrides Sub AcquireConnections(ByVal BeginTransaction As Object)
connMgr = Me.Connections.connection
oledbconn = CType(connMgr.AcquireConnection(Nothing), OleDb.OleDbConnection)
MyBase.PostExecute()
End Sub
Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)
With oledbCmd
.Parameters("@BusinessEntityID").Value = Row.BusinessEntityID
.Parameters("@FirstName").Value = Row.FirstName
.Parameters("@MiddleName").Value = Row.MiddleName
.Parameters("@LastName").Value = Row.LastName
.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 PostExecute()
oledbtran.Commit()
oledbtran = oledbconn.BeginTransaction()
oledbCmd.Transaction = oledbtran
MyBase.PostExecute()
End Sub
Public Overrides Sub ReleaseConnections()
MyBase.ReleaseConnections()
End Sub
End Class
