從ORACLE向SQL SERVER定時遷移作業的設計與實現

　　一問題描述
　　
　　
　　
　　某事業單位很早以前開發了一套基於Oracle數據庫的管理系統工作在WINDOWS 下采用C/S工作模式數據庫的字符集為WEISOP由於工作需要需開發一套在此基礎上的查詢系統為保證原系統的安全和完整性要求查詢系統不得直接使用原數據庫影響目前系統的運行只能通過中間件技術實現查詢系統對原數據庫的訪問同時由於原系統在使用過程中發現數據存取的速度很慢要求查詢系統使用SQL SERVER 數據庫進行查詢
　　
　　
　　
　　二解決方案分析
　　
　　
　　
　　根據用戶的需求和原系統的工作模式可采用的方案主要有以下三種
　　
　　
　　
　　　利用SQL SERVER 的作業調度功能定時執行數據遷移實現數據同步
　　
　　
　　
　　DTS（數據轉換服務）是微軟從SQL SERVER 開始引入的DTS的主要目的是在系統之間遷移數據和數據庫對象DTS原來是用作SQL SERVER OLAP服務的ETL工具後來微軟意識到DTS 不僅可以作為OLAP 服務的數據抽取和載入工具還可以實現異種數據庫間的遷移因此擴充了DTS的功能在SQL SERVER 中提供了簡單易用的DTS 設計器利用DTS設計器可以很方便地解決本文涉及的問題但是如果要遷移的對象比較多利用DTS設計器的工作量就相當大了因此提出了第二種解決方案
　　
　　
　　
　　　利用DTS 編程實現數據的定時遷移
　　
　　
　　
　　該方法原理簡單但需要對 DTS 有一定的了解性能也比較好熟悉VBVCDELPHI等任一種編程語言均可以利用SQL Server 提供的 DTS COM接口實現數據的遷移
　　
　　
　　
　　DTS 遷移規范保存在一個稱為包的實體中DTS包是基本的DTS組件的容器這些組件包括連接任務轉換工作流不同的組件完成不同的功能它們共同構成數據遷移的實現主體要通過DTS編程實現數據庫的遷移至少需要兩個連接對象其中一個提供數據一個接收數據至少需要一個轉換對象完成數據從源到目的服務器的轉換至少需要兩個任務對象完成遷移之前的目的服務器上的數據表的刪除和重建至少需要三個工作流對象為遷移工作設計執行的步驟
　　
　　
　　
　　為了實現定時執行程序還要完成對SQL SERVER AGENT 進行編程實現遷移作業的提交和調度由於SQL SERVER 的作業調度是通過 SQL SERVER AGENT 來管理的因此需要在啟動SQL SERVER 時同時啟動SQL SERVER AGENT
　　
　　
　　
　　　采用中間件技術
　　
　　
　　
　　前面兩種方案都是利用DTS離不開SQL Server 的DTS利用中間件技術可以通過實現一服務程序定時將數據從ORACLE服務器取出然後轉換成SQL SERVER 數據庫的數據格式傳入SQL SERVER其工作原理如下圖
　　　
　　該技術可以通過通過ODBC 或OLE DB技術編程實現數據的定時獲取和轉換傳出對於編程的工作量較大原因在於ORACLE 和SQL SERVER的數據類型的不一致必須通過類型轉換實現數據的一致同時效率也比較低由於作為一種服務程序長駐內存對程序的質量要求至少不得出現內存洩露否則可能使服務器癱瘓不過這種方案的好處在於可以脫離SQL SERVER 維護的工作量相對要輕一些
　　
　　
　　
　　比較上述的三種方案從實現的難度上比較第一種最低最後一種最高從效率上比較最後一種最低第一種與第二種最高從可維護性來比較第一種最低最後一種最高
　　
　　
　　
　　綜合三種方案筆者認為第二中方案較好發布到目標系統上只需在現場運行一次數據遷移的任務安裝程序就能實現SQL Server 定時從Oracle服務器遷移數據同時所有的工作量也只是選擇要遷移的數據表該工作如果要遷移的表是已知的甚至可以從文件中直接讀入就能實現任務的安裝
　　
　　
　　
　　下面介紹采用第二種方案用VB編寫在SQL SERVER上能定時自動進行數據遷移任務的安裝程序的方法
　　
　　
　　
　　三數據遷移的實現
　　
　　
　　
　　為了能在目標機上順利實現數據遷移將DTS包存儲到SQL SERVER在SQL SEVRE AGENT　的作業調度中采用DTSRUN 來加載和執行DTS包這樣所有的工作只需作一次就可將整個數據遷移的DTS包和SQL SERVER的作業發布到目標機上
　　
　　
　　
　　（一）算法設計
　　
　　
　　
　　程序的流程圖如下
　　　
　　（二）關鍵技術說明
　　
　　
　　
　　要實現數據的遷移必須考慮兩個問題第一數據的遷移要求目標系統上的數據與ORACLE　數據庫中的數據要一致因此目標數據庫中的相應表必須在遷移之前被刪除所以遷移任務的第一個是對相應表執行刪除的SQL 任務第二由於目標表被刪除遷移的數據失去寄托因此遷移任務的第二步必須在目標系統上重建相應的表在建表時由於ORACLE 數據庫的數據類型與SQL SERVER 不一致因此必須進行類型轉換它們之間的對應關系和轉換要求如下表
　　　
　　（三）程序設計
　　
　　　界面設計如圖所示
　　
　　　編碼
　　
　　
　　
　　() 在整個程序運行過程中需要兩個全局變量DTS 包 oPackage 和Oracle 服務器連接 oraCon
　　
　　
　　
　　當設置好連接參數後單擊連接Oracle服務器將與 ORACLE 服務器連接並取出所有表然後填充第一個列表框並在下面表格中顯示相應的數據（如果選中了顯示數據）
　　
　　
　　
　　Private Sub Command_Click()
　　
　　
　　
　　Dim rst As New ADODBRecordset
　　
　　
　　
　　With oraCon
　　
　　
　　
　　　Provider = OraOLEDBOracle
　　
　　
　　
　　　Properties(User ID) = Text()Text
　　
　　
　　
　　　Properties(Password) = Text()Text
　　
　　
　　
　　　Properties(Data Source) = Text()Text
　　
　　
　　
　　　Properties(Persist Security Info) = True
　　
　　
　　
　　　Open
　　
　　
　　
　　End With
　　
　　
　　
　　　　　　　　
　　
　　
　　
　　With rst
　　
　　
　　
　　　Source = select * from all_tables where tablespace_name<>SYSTEM order by ownertable_name
　　
　　
　　
　　　ActiveConnection = oraCon
　　
　　
　　
　　　CursorType = adOpenKeyset
　　
　　
　　
　　　LockType = adLockOptimistic
　　
　　
　　
　　　Open
　　
　　
　　
　　End With
　　
　　
　　
　　RefreshGrid rst
　　
　　
　　
　　FillTabList rst
　　
　　
　　
　　rstClose
　　
　　
　　
　　CommandEnabled = False
　　
　　
　　
　　End Sub
　　
　　
　　
　　() 在第一個列表框中雙擊數據表列表將選中的表加入第二個列表如果顯示數據被選中將顯示相應表的數據同時顯示表的結構信息在第二個列表框中雙擊數據表將選中項移出該列表框
　　
　　Private Sub List_DblClick()
　　Dim rst As New ADODBRecordset
　　Dim strSQL As String
　　Dim tmpStr As String
　　ListAddItem (ListList(ListListIndex))
　　
　　If CheckValue = Then
　　　With rst
　　　　 Source = select * from & ListList(ListListIndex)
　　　　 ActiveConnection = oraCon
　　　　 CursorType = adOpenKeyset
　　　　 LockType = adLockOptimistic
　　　　 Open
　　　End With
　　
　　　RefreshGrid rst
　　　rstClose
　　End If
　　
　　tmpStr = ListList(ListListIndex)
　　strSQL = SELECT COLUMN_ID COLUMN_NAME DATA_TYPE DATA_LENGTH DATA_PRECISION DATA_SCALE
　　strSQL = strSQL & FROM SYSALL_TAB_COLUMNS WHERE TABLE_NAME=
　　strSQL = strSQL & & Mid(tmpStr InStr( tmpStr vbTextCompare) + ) &
　　strSQL = strSQL & and OWNER= & Mid(tmpStr InStr( tmpStr vbTextCompare) ) &
　　With rst
　　　Source = strSQL
　　　ActiveConnection = oraCon
　　　CursorType = adOpenKeyset
　　　LockType = adLockOptimistic
　　　Open
　　End With
　　
　　rstMoveFirst
　　RefreshPropGrid rst
　　
　　rstClose
　　
　　PropGridVisible = True
　　SQLScriptListVisible = False
　　
　　End Sub
　　Private Sub RefreshGrid(rst As ADODBRecordset)
　　
　　Dim fld As ADODBField
　　
　　On Error Resum
From:http://tw.wingwit.com/Article/program/SQLServer/201311/21974.html