異構庫環境的ETL,oracle VS mssql

1、環境需求描述：

使用者內網有一台oracle 10g庫，外網有一台oracle 9i。使用者通過內網發布人才招聘相關資訊，所有的資訊在一張表裡面，運行一個export/import指令碼把內網該表同步到外網表，內網和外網之間應該 是建立了暫存資料表lb_retain，這張表是作為外網應用使用。該使用者開發的新CMS系統ASP.NET），資料庫是採用MSSQL SERVER 2005 X64，該資料庫也是位於外網環境中。由於使用者發布招聘資訊不想在新CMS平台上進行，所以不得不對這一資訊進行整合。最終要使該招聘資訊能在新網站上顯 示給終端使用者。

2、解決思路：

分析以上需求，即源oracle庫同 步到目標mssql 2005，為了達到異構庫環境的表與表之間的資料同步，本次解決方案是採用了MSSQL商業智慧解決方案，用SQL Server Business Intelligence Development Studio工具建立Integration Services 項目建立DTS包，該包可以處理非常複雜的資料採礦工作如果源是mssql，目標是oracle的話，可以使用oracle的透明網關）。其中，我建立 從oracle抽取資料並插入到新網站所對應的MSSQL表招聘裡面。這樣就達到了整合的效果，只要拿到了資料插入到新應用所在的庫裡面去，就可以整合到 了新應用的所有功能，而不需要二次開發，也不需要寫應用程式層代碼來操作oracle庫了。

3、解決過程

(1)建立Integration services項目

650) this.width=650;" height="424" border="0" src="http://www.bkjia.com/uploads/allimg/131229/1U9533049-0.png" alt="clip_image001" title="clip_image001" style="border-bottom: 0px; border-left: 0px; border-top: 0px; border-right: 0px" />

(2)SSIS匯入匯出嚮導--建立基本的資料庫連接串以及基本的SQL，選擇源

650) this.width=650;" height="423" border="0" src="http://img1.51cto.com/attachment/201212/1/1645600_1354347379Mcn3.png" alt="clip_image002" title="clip_image002" style="border-bottom: 0px; border-left: 0px; border-top: 0px; border-right: 0px" />

3）選擇目標

650) this.width=650;" height="410" border="0" src="http://www.bkjia.com/uploads/allimg/131229/1U9535944-2.png" alt="clip_image003" title="clip_image003" style="border-bottom: 0px; border-left: 0px; border-top: 0px; border-right: 0px" />

4）編寫SQL語句

650) this.width=650;" height="380" border="0" src="http://www.bkjia.com/uploads/allimg/131229/1U953M44-3.png" alt="clip_image004" title="clip_image004" style="border-bottom: 0px; border-left: 0px; border-top: 0px; border-right: 0px" />

5）建立源列到目標表的列的映射

650) this.width=650;" height="416" border="0" src="http://www.bkjia.com/uploads/allimg/131229/1U953Ca-4.png" alt="clip_image005" title="clip_image005" style="border-bottom: 0px; border-left: 0px; border-top: 0px; border-right: 0px" />

6）下一步，警告先不管

650) this.width=650;" height="339" border="0" src="http://www.bkjia.com/uploads/allimg/131229/1U9533R4-5.png" alt="clip_image006" title="clip_image006" style="border-bottom: 0px; border-left: 0px; border-top: 0px; border-right: 0px" />

7）分別添加“資料轉換”、“衍生的資料行”、“指令碼組件”

“資料轉換”把源ORACLE的varchar2列轉換為MSSQL所相容的列

650) this.width=650;" height="395" border="0" src="http://www.bkjia.com/uploads/allimg/131229/1U9531023-6.png" alt="clip_image007" title="clip_image007" style="border-bottom: 0px; border-left: 0px; border-top: 0px; border-right: 0px" />

8）“衍生的資料行”

根據你的業務不同配置也是不太一樣的，這裡所要添加“衍生的資料行”是因為源ORACLE庫沒有與目標庫對應的列，所以要在同步的過程中產生新列並賦初值

650) this.width=650;" height="418" border="0" src="http://www.bkjia.com/uploads/allimg/131229/1U953D14-7.png" alt="clip_image008" title="clip_image008" style="border-bottom: 0px; border-left: 0px; border-top: 0px; border-right: 0px" />

9）“指令碼組件”

“指令碼組件”是為了處理比較複雜的商務程序，比如本方案有一個欄位是要自增的，所以要編寫指令碼

設定輸入列

設計指令碼：

ScriptMain:

' Microsoft SQL Server Integration Services user script component

' This is your new script component in Microsoft Visual Basic .NET

' ScriptMain is the entrypoint class for script components

Imports System

Imports System.Data

Imports System.Math

Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper

Imports Microsoft.SqlServer.Dts.Runtime.Wrapper

Public Class ScriptMain

Inherits UserComponent

Dim intCounter As Integer = 1 'Set intial value here

Public Overrides Sub 輸入0_ProcessInputRow(ByVal Row As 輸入0Buffer)

Row.Taxis = intCounter

intCounter = intCounter + 1 ' Set the incremental value here

End Sub

End Class

BufferWrapper

' THIS IS AUTO-GENERATED CODE THAT WILL BE OVERWRITTEN! DO NOT EDIT!

' Microsoft SQL Server Integration Services buffer wrappers

' This module defines classes for accessing data flow buffers

' THIS IS AUTO-GENERATED CODE THAT WILL BE OVERWRITTEN! DO NOT EDIT!

Imports System

Imports System.Data

Imports Microsoft.SqlServer.Dts.Pipeline

Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper

Public Class 輸入0Buffer

Inherits ScriptBuffer

Public Sub New(ByVal Buffer As PipelineBuffer, ByVal BufferColumnIndexes As Integer())

MyBase.New(Buffer, BufferColumnIndexes)

End Sub

Public Property [Taxis]() As Int32

Get

Return CType(Me(0), Int32)

End Get

Set

Me(0) = Value

End Set

End Property

Public Property [Taxis_IsNull] As Boolean

Get

Return IsNull(0)

End Get

Set

SetNull(0)

End Set

End Property

Public Function NextRow() As Boolean

NextRow = MyBase.NextRow()

End Function

Public Function EndOfRowset() As Boolean

EndOfRowset = MyBase.EndOfRowset

End Function

End Class

ComponentWrapper

' THIS IS AUTO-GENERATED CODE THAT WILL BE OVERWRITTEN! DO NOT EDIT!

' Microsoft SQL Server Integration Services component wrapper

' This module defines the base class for your component

' THIS IS AUTO-GENERATED CODE THAT WILL BE OVERWRITTEN! DO NOT EDIT!

Imports System

Imports System.Data

Imports Microsoft.SqlServer.Dts.Pipeline

Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper

Imports Microsoft.SqlServer.Dts.Runtime.Wrapper

Public Class UserComponent

Inherits ScriptComponent

Public Connections As New Connections(Me)

Public Variables As New Variables(Me)

Public Overrides Sub ProcessInput(ByVal InputID As Integer, ByVal Buffer As PipelineBuffer)

If InputID = 1572 Then

輸入0_ProcessInput(New 輸入0Buffer(Buffer, GetColumnIndexes(InputID)))

End If

End Sub

Public Overridable Sub 輸入0_ProcessInput(ByVal Buffer As 輸入0Buffer)

While Buffer.NextRow()

輸入0_ProcessInputRow(Buffer)

End While

End Sub

Public Overridable Sub 輸入0_ProcessInputRow(ByVal Row As 輸入0Buffer)

End Sub

End Class

Public Class Connections

Dim ParentComponent As ScriptComponent

Public Sub New(ByVal Component As ScriptComponent)

ParentComponent = Component

End Sub

End Class

Public Class Variables

Dim ParentComponent As ScriptComponent

Public Sub New(ByVal Component As ScriptComponent)

ParentComponent = Component

End Sub

End Class

10）開啟“目標”的資料流

建立映射關係

650) this.width=650;" height="645" border="0" src="http://www.bkjia.com/uploads/allimg/131229/1U9532619-8.gif" alt="clip_image009" title="clip_image009" style="border-bottom: 0px; border-left: 0px; border-top: 0px; border-right: 0px" />

11）設定一下ETL包，不然等下添加JOB的時候有可能出錯

“項目屬性”-“調試”-“調試選項”-Run64BitRuntime=false(後面講解釋原因)

包的加密方式用-EncryptSensitiveWithPassword

650) this.width=650;" height="318" border="0" src="http://www.bkjia.com/uploads/allimg/131229/1U95360P-9.png" alt="clip_image010" title="clip_image010" style="border-bottom: 0px; border-left: 0px; border-top: 0px; border-right: 0px" />

12）直接執行該包，看結果，沒有錯誤，成功後就開始把它加入到MSSQL的JOB

開啟SQL Server Management Studio並開啟SQL SERVER代理，新增作業，詳細怎麼去建立可以做官網去查文檔，這裡只講關鍵步驟：

650) this.width=650;" height="398" border="0" src="http://www.bkjia.com/uploads/allimg/131229/1U9536405-10.png" alt="clip_image011" title="clip_image011" style="border-bottom: 0px; border-left: 0px; border-top: 0px; border-right: 0px" />

在命令視窗裡面輸入：

讓DTExec.exe這個應用程式去調用剛剛建立好的ETL.DTSX包，/DECRYPT 123456為該包的密碼。

最後，對11）中的Run64BitRuntime=false做解釋，這樣動作表示將包做為32位處理。按官方說法我們只能建立一個32位的ETL.DTSX包

官方說法：

此外，某些 OLE DB 提供者和某些 ADO.NET 提供者停用 64 位元版本。如果在包中使用這些提供者，64 位元 SQL Server Agent程式通過使用 64 位元 DTExec.exe 公用程式運行包時出現串連的各種問題。

然後,調度 64 位元 SQL Server Agent程式必須通過使用 32 位版本的 DTExec.exe 公用程式運行包。

官方理解，調度 64 位元 SQL Server Agent程式：http://support.microsoft.com/kb/934653/zh-cn

本文出自 “海斌的技術部落格” 部落格，轉載請與作者聯絡！