透過 DTS 服務彙整企業多種資料來源簡單化多資料庫資料整合作業

**作者：**台灣微軟資料庫講師楊志強

本頁內容

前言
瞭解 SQL Server 進行資料轉換的工具
介紹 DTS 的基本功能
介紹 ActiveX Script 定義資料轉換
透過 DTS 結合前端應用程式進行資料轉換
使用 DTS 建置資料倉儲資料庫概念介紹
結論

前言

企業中日常交易所使用的資料庫會根據前端系統的選擇而有所差異，最常使用到的資料庫舉凡有 Microsoft SQL Server、Oracle Database、IBM DB2、Sybase、Informix… 以及其他如 MYSQL、Progress、Access 等都是不錯的選擇。營運系統在日常作業所產生的資料，都偏重於交易資訊記錄與資料處理的正確性，例如門市銷售系統（POS）記錄客戶購買商品類別、消費金額與付款方式，著重於單據輸入與系統反應時間；客戶關係管理系統（CRM）記錄客戶反應問題種類、問題單追蹤與結案進度，強調處理流程的績效管理。當主管想整體瀏覽客戶購買產品後的使用狀況，資訊人員必須從門市銷售系統抓取客戶消費資訊與客戶關係管理系統的資料進行整合，透過複雜的程式，找出主管需要的資訊。這樣的作法不僅浪費人力在處理資料轉換，更容易造成資料的重複與不一致的問題發生。

資料處理接下來的趨勢就是企業將面臨如何隨著日益變化企業環境與配合複雜營運模式，彙整各個異質系統資料以符合使用者所提需求，這也是企業邁入商業智慧（Business Intelligence）的首要步驟。

瞭解 SQL Server 進行資料轉換的工具

使用 BCP 用戶端大量複製公用程式 (Bulk Copy Program)，可將大量新的資料列插入 SQL Server 資料表或是將資料從資料庫中快速的轉出成文字格式或是原生格式。此公用程式使用時必須先瞭解被大量複製的資料表之結構，以及資料表中的資料列有效的資料型別。一般使用 BCP 應用程式進行資料移轉時可以區分成以下的使用時機。

1. 在伺服器之間複製資料

   若要將資料大量複製到另一個 SQL Server 資料庫，來源資料庫的資料必須先大量複製到檔案，然後再將檔案大量複製到目的資料庫。BCP 可使用原生、字元和 Unicode 格式在不同處理器架構的不同 SQL Server 執行個體 (Instance) 上大量複製資料。不過，在匯出和匯入時必須使用相同的格式。非字元的資料使用原生 (Native) 格式的使用可節省時間，避免在資料型別與字元格式之間，不必要的來回轉換。當您在使用不同字碼頁 (Code Page) 的伺服器之間大量載入資料時，可針對所有字元資料使用 Unicode 字元格式，如此將可避免遺失任何延伸字元 (倘若延伸字元被複製到非 Unicode 資料行，將無法表示延伸的字元，仍可能遺失字元)。

2. 將資料檔的資料複製到伺服器

   透過 BCP 將資料檔案中的資料快速複製到伺服器中，複製過程中切記遵循下列準則

   • 當大量資料複製到沒有包含索引的資料表時，建議將資料庫的復原模型設成大量記錄。使用此選項來防止交易記錄檔耗用所有硬碟空間。

   • 若要載入的資料量相對上大於已經存在資料表內的資料量，可在執行大量複製作業之前先卸除資料表內的索引來加快速度。反過來說，若要載入的資料相對上小於已經存在於資料表的資料量，卸除索引就不是很有必要，因為重建索引所需的時間可能比執行大量複製作業的時間長。

   • 平行地將多個用戶端的資料大量複製到一個資料表內。這可改善資料載入作業的效能。若要平行地將資料大量複製到 SQL Server 的執行個體中通常資料庫使用大量記錄復原並指定 TABLOCK 提示。

3. 將資料從檢視表複製成檔案

   複雜的 JOIN 查詢結果，可以先將查詢指令轉換成檢視表，並將結果以檔案格式輸出。使用範例如下

   BCP XDB.DBO.MY_VIEW out C:\MY_VIEW.TXT -c -Sservername -Usa -Ppassword

4. 將查詢的資料複製到資料檔

   BCP 公用程式可將 SQL 陳述式的結果集複製到檔案檔中。陳述式可以是任何能夠傳回結果集的有效陳述式，分散式查詢或聯結多個資料表的 SELECT 陳述式。例如，若要依照工號的順序，從 Northwind 資料庫的員工資料表中，將所有的姓名名稱複製到 EMP.TXT 資料檔中，請在命令提示下執行：

   bcp "SELECT firstname,lastname FROM northwind.dbo.employees ORDER BY lastname"
   queryout EMP.TXT -c -Sservername -Usa -Ppassword

   若 SQL 陳述式傳回多個結果集，例如指定 COMPUTE 子句的 SELECT 陳述式，或包含多個 SELECT 陳述式的預存程序執行，將只會複製第一個結果集，接下來的結果集將被略過。

BCP 公用程式所在位置為 C:\Program Files\Microsoft SQL Server\80\Tools\Binn\bcp.exe 中，該公用程式可以與系統檔案如 .BAT 與 .CMD 結合，實作成用戶端的自動化轉檔程式。使用方法如下

參數說明

-f ：使用格式畫檔案並記錄有關儲存型別、前置詞長度、欄位長度和欄位與資料列終端子的相關資訊

-c ：將結果以文字格式處理，這是最常使用的格式，其他 -n 使用原生 (Native)資料格式處理牽涉到無延伸 /DBCS 字元，Unicode 字元使用 -w 參數處裡 Unicode 資料/無延伸 /DBCS 字元，Unicode 原生 (Native) 使用 -N 處理牽涉到延伸 /DBCS 字元

-U ：指明使用那個登入帳號，一般都是要與 -P 指明帳號所屬密碼

-T ：使用信任是連結方式與資料庫進行連結

-h ：運用在平行大量載入資料時使用

一般使用 BCP 輸出成文字資料，使用範例

C:\>BCP XDB.OWNER.OBJECT out c:\Object.txt –c –Uxxx –Pyyy –Szzz

使用 BCP 載入文字格式的範例如下，使用範例

C:\>BCP XDB.OWNER.OBJECT in c:\Object.txt –c –Uxxx –Pyyy –Szzz

另外一種大量複製資料方式是使用 BULK INSERT 陳述式，它也可以將資料從資料檔載入到資料表。但是 BULK INSERT 陳述式無法將資料從 SQL Server 執行個體大量複製檔案到資料檔，僅為單向處理作業。BULK INSERT 陳述式可使用 BCP 公用程式的功能，透過 SQL 陳述式將資料大量複製到資料庫中，而不是從命令提示字元來進行。

使用時資料需置放於 Server 端，消除從用戶端應用程式到伺服器之間通訊資料的負擔，執行權限僅侷限於系統管理員 (sysadmin) 與 bulkadmin 伺服器固定角色的成員能夠執行。使用說明如下

參數說明

BATCHSIZE：指定批次中的列數，每批次皆視為一次交易複製到伺服器中。當失敗時，SQL Server 會認可或復原批次的交易，例如當設定 2000 時，表示每 2000 筆資料便進行 commit，若發生交易中斷或是失敗僅會回覆最後失敗的筆數，這樣的設定可以降低資料庫發生載入失敗時，需要進行大量資料的復原的負擔。

FIELDTERMINATOR：指定要用於 char 與 widechar 資料檔中的欄位終端子。預設值是 \t (tab 字元)。

FORMATFILE：指定格式檔的完整路徑，可以與 BCP 共用相同的檔案

TABLOCK：大量複製作業期間需要資料表層級鎖定。指定 TABLOCK，則資料表可被同時載入，大幅改善效能。

一般使用 Bulk Insert 載入文字資料到資料表，使用範例如下

透過 Bulk Insert 使用的原始文字檔案資料

設定 Bulk Insert 的指令與對應參數

透過 Bulk Insert 載入後執行結果

介紹 DTS 的基本功能

資料轉換服務 (Data Transformation Services，DTS) 提供一組工具，讓程式設計人員能夠擷取、轉換、及合併不同來源的資料，成為單一連接工具可支援的各種目的資料表，以解決重要的商業需求。透過使用圖形化方式建立 DTS 封裝的 DTS 工具，或者使用者可撰寫程式的方式來設計封裝的 DTS 物件模型，作為企業級自動化資料轉換的工具。DTS 主要的轉換過程如下所示

資料轉換服務的工具包含下列幾項

• DTS 匯入/匯出精靈

  透過 DTS 匯入/匯出精靈，可製作簡單的匯入與匯出 Prototype，完成後可以再利用 DTS 設計師進行修改，使用匯入/匯出精靈可以搬移架構 (Schema)/資料 (Data) 另一個地方，進行 Tables / Views 資料搬移、Ad Hoc 查詢資料搬移與tables, stored procedures, constraints, indexes 定義與資料搬移等。搬移的過程中可以加入自訂的 Script 語言 (VB Script/ JScript) 來撰寫複雜的轉換，並可搭配 SQL Server Agent 的排程設定自動執行作業，最後製作完成的 Prototype 可以依照需儲存成 SQL Server 中、Meta Data Services 、Visual Basic 檔案與結構化檔案等不同方式。

  啟動匯入/匯出精靈可以透過命列的方式 C:\Program Files\Microsoft SQL Server\80\Tools\Binn \dtswiz.exe 啟動或是透過程式集中的『匯入和匯出資料』進行啟動。

  

  啟動匯入/匯出精靈後的畫面

  

  選擇轉換的資料來源，可以是 OLEDB 或是 ODBC 所支援的資料來源

  

  選擇轉換的資料目的地，可以是 OLEDB 或是 ODBC 所支援的資料來源

  

  選擇直接複製資料表與檢視表、使用查詢傳送資料、進行物件與資料的複製

  

  選擇需要轉換的資料表或是檢視表

  

  利用 Script 語言進行資料的特殊轉換設定

  

  可將 DTS 封裝，透過排程執行並加以儲存在適當的位置

  

  透過匯入/匯出精靈可以將結果直接執行並看到執行狀態

• DTS 設計師

  直覺與圖型化的方式完成資料轉換服務，可以建立簡單的封裝、建立包含複雜工作流程的封裝(例如，包含分枝、多重步驟、多重連線、複雜邏輯、以及事件導向程式碼等的封裝)與編輯現有的封裝等。DTS 設計師的圖形使用者介面可以使用拖放的方式，完成組成封裝之不同 DTS 物件屬性表的方式，來建立並設定封裝。使用者介面包含有：

  連線 (Connection) 設定，可以根據不同資料來源進行設定

  

  工作(Task)，從基本資料轉換到複雜的作業組成一個完整的作業套件

  

  工作流程(Workflow)，優先條件約束允許根據第一項的工作是否執行、執行成功、或執行失敗來連結兩個工作，也可以使用優先條件約束在工作流程中建立條件分支，或是沒有條件約束的步驟會立即執行，並且數個步驟可以平行執行。

  

• 封裝執行公用程式

  資料轉換的封裝可以透過兩個封裝執行公用程式之一來執行，分別 DTS Run 圖形公用程式和 DTSRUN 命令提示公用程式，DTS Run 圖形公用程式可以透過 C:\Program Files\Microsoft SQL Server\80\Tools\Binn\dtsrunui.exe 執行，啟動後可以選擇需要執行的封裝名稱與設定進階屬性

  

  啟動 DTS Run 圖形公用程式的畫面，選擇需要執行的封裝名稱並可直接執行

  

  進階屬性中可以指定變數的設定值、執行記錄檔與產生 DTSRUN 命令提示字串

DTSRUN 公用程式可執行由資料轉換服務 (DTS) 建立的封裝。該封裝可儲存在 msdb 資料庫、COM 結構化儲存體檔案，或 Meta Data Services 中，它是 DOS 命令列的封裝執行程式，使用說明如下

參數說明

/F: 適用執行 COM 結構化儲存體封裝程式檔案

/R: 適用執行 Meta Data Services 封裝

/S: 適用執行 msdb 與 Meta Data Services 所在的伺服器名稱

/N: 指定需要執行的封裝名稱

/Z: 指定使用加密後的執行字串進行執行

下列執行範例是執行封裝名稱為 PARA_DTS，使用參數 CUSTOMERID 屬於 String(8) 格式，並指明不寫入到 Windows Event Log , 與本機伺服器連結使用信任式連結方式。

介紹 ActiveX Script 定義資料轉換

在資料轉換服務中，藉著使用 ActiveX Script 實作 DTS 物件模型的物件、屬性、方法與定序 (Collation) 來擴充 DTS 封裝的功能，ActiveX Script 的功能有

• 可在資料從來源複製到目的地時將它格式化及轉換、寫入使用條件式邏輯的函數來管理封裝工作流程

• 可建立資料列處理資料的函數、使用及修改儲存在 DTS 全域變數的值

• 可操作針對資料存取及公用程式功能所建立的 COM 物件

• 可建立及使用 ADO 連線、命令、資料錄集及其他物件來存取及操作資料等功能

但是必須特別注意利用 ActiveX Script 語言輔助 DTS 的轉換作業時，因為執行時才編譯，故降低了效能，並且每次執行封裝呼叫工作時就會執行該指令碼一次，因此以效能為優先的資料轉換作業，切記要注意 ActiveX Script 的使用。以下的範例就是使用 ActiveX Script 進行資料的 LOOKUP 的操作。

除了使用 ActiveX Script 將資料從來源複製到目的地時將它格式化及轉換之外，DTS 仍有提供轉換資料的種類包括既有的 ActiveX Script 還有 Copy Column、Date-Time String、Lowercase String、Uppercase String、Middle of String、Trim String、Read File 與 Write File 等功能。

下圖說明 DTS 除搭配彈性化的使用，可以讓資料轉換變得更具靈活，一般的『一對一』轉換、還有『相稱性的多對多』與『非相稱性的』等形式的轉換。

透過 DTS 結合前端應用程式進行資料轉換

資料轉換服務除了一般的例行性轉換作業與排程設定之外，仍可透過前端應用程式的整合，強化應用的層級。首先將介紹 DTS 與 OPENROWSET 函數結合。透過 OPENROWSET 呼叫封裝可以將最終結果進行資料處理當成 RESULT SET，過程中 Provider 必須使用 DTSPackageDSO，使用方法可以 SELECT *或 SELECT * FROM <package name>，下面的範例是透過 OPENROWSET 函數將 DTS 資料源結合 SQL 指令進行輸出。

再來，DTS 可以與 Linked Server 結合查詢封裝資料，首先必須要透過 sp_addlinkedserver 方法將封裝註冊到 master.dbo.sysservers 中，並使用 DTSPackageDSO 為資料的提供者。以下的範例就是透過 Linked Server 將 C:\CUSTOMERS.dts 的封裝程式設定成連結伺服器，再透過 SQL 直接進行資料的查詢。

其次，封裝程式的執行可以透過 VBScript 的 COM 元件呼叫方式，直接在前端應用程式執行，使用 DTS.Package 物件的 LoadFromSQLServer 執行資料庫中封裝程式，LoadFromStorageFile 執行檔案中封裝程式。下列的範例是利用 VBScript 執行檔案中封裝程式的範例

使用 DTS 建置資料倉儲資料庫概念介紹

資料倉儲(Data Warehouse)可以解決企業龐大異質資料的整理、歸納、合併與儲存，所建構成的一個具有系統性資料集合。它好像是個超大型的物流中心，裡面的貨品(資料)來自各個不同的供應商(各個營運系統)，經過搬運、彙整與排列(淬取轉換載入管理--ETLM 工具)將貨品有系統地放到不同貨架(維度)並進行數量(量值)的記錄與盤點，方便顧客(決策者)依照喜歡的方式與途徑進行商品的購買(分析資料與報表產生)。

DTS 設計師可以將多種資料來源透過單一的整合封裝，彙整到 Analysis Service 的 Cube 中，可以透過 Cube 的彙總功能，事先為各維度組合所需的數值，先行計算並儲存。這樣可以加速使用者查詢彙總資料的速度，也就是說方便決策者查詢特定年度中、特定產品在特定地區的銷售業績，這樣的查詢著實利用三個維度-年度、產品、地區與彙總後的銷售業績。在 Cube 中預先計算所有可能的彙總可以讓所有的查詢獲得最快的回應時間。然而，彙總作業可能需要相當大量的儲存空間及處理時間。以下的 Cube 就是可以讓決策者依照既定的維度進行資料的分析，過程中必須搭配 DTS 封裝的設計輔助完成。

結論

進行資料處理的重點在於透過適當的工具可以節省轉換時間，也可以強化設定的彈性，例如利用 BCP 可以輸出與載入大量固定格式的資料，使用 Bulk Insert 可以用 SQL 指令方式進行資料的轉換作業。使用 DTS 可以進行轉換、合併、置換工作，利用輸入/輸出精靈簡單製作轉換的 Prototype，再搭配 DTS 設計師可以微調修改轉換的內容，最後可以利用 SQL Server Agent 搭配 DTSRUN 進行自動化排程。透過資料轉換工具整合異質資料來源，將是企業邁向商業智慧過程中的首要工程。