如何將Oralce資料匯入SQL Server或Excel檔案

SQL Server有提供資料匯入及匯出精靈可以方便IT人員在不需要Coding的方式下進行資料搬遷

但資料來源若是異質資料庫要怎麼連線?要做那些設定?

今天就以連線至Oracle進行示範

連線至Oracle需要以下幾個步驟

  1. 安裝Oracle driver
  2. 設定tnsname

第1:安裝Oracle driver時,請Google Oracle Data Access Components (ODAC), 目前版本為(12.2.0.1.0)要注意只能安裝32bit driver

下載URL: http://www.oracle.com/technetwork/topics/dotnet/utilsoft-086879.html

下載檔案:ODTwithODAC122010.zip,解壓縮後請執行Setup.exe,會開始跑Oracle Universal Installer檢查,需要等一下

安裝時就Step by Step都按下一步即可,特別注意在步驟3安裝的路徑,個人喜好是將driver安裝在C:(預設可能是D或其他),您可以自行修改

步驟4若目前環境沒有安裝Visual Studio請移除第3項(Oracle developer tools for Visual Studio),否則會有錯誤訊息

步驟6 DB Connection Configuration,直接按下一步,我們之後再設定即可

安裝完成

 

第2:設定tnsname.ora

先將範例tnsname.ora(C:\app\client\<youruser>\product\12.2.0\client_1\Network\Admin\Sample)複製至上一層

使用Notepad打開tnsname.ora,先複製第1段之後開始修改來源名稱<data source alias>、主機名稱<hostname or IP>、Port<port>、SID(原始為Service name若您不知道Service name建議使用SID)

第3:將資料移轉至SQL Server

接下來以Oracle範例資料庫HR進行示範,先在SQL Server新增HR Database

在HR上點選右鍵,工作(Task)-->匯入資料(Import Data)

進入到資料匯入及匯出精靈,點選下一步

選擇資料來源,注意下拉時要選擇Oracle Provider for OLE DB,若沒有看到此選項時建議檢查ODAC安裝的版本

點選設定(Properties)輸入剛剛在tnsname.ora設定的名稱及連線帳號密碼後點選測試連線(Test Connection)

若出現此訊息表示連線成功,若無法連上的話可能原因有很多就不再一一說明

目的地請選擇SQL Servere Native Client並輸入帳號及密碼,下一步

在此步驟可以選擇將整個Table或View移轉過來或執行SQL語法過濾掉不要的資料,我們先選第1項

選擇HR的Table,因為Oracle的Numeric資料型態在精靈中轉換會失敗,會需要由Visual Studio中的SSIS專案進行修改,故我們只挑選EMPLOYEES這個沒問題的Table進行移轉

選擇立即執行,並勾選存檔以便之後可以再執行

執行後可以看到轉入107筆資料

若要再執行要連線Integration Service

在MSDB下可以看到剛剛存檔的封裝

SQL Server內建的資料匯入及匯出精靈雖然可以執行但有點陽春,若想要進階運用的話建議使用Visual Studio開發SSIS專案,可以進行欄位型態轉換,錯誤處理等進階功能。