SQL Server 2000中的資料轉換服務 (DTS)

server|資料|轉換

摘要： 為了完成資料合併、存檔和分析等任務；為了進行應用程式開發；為了進行資料庫或伺服器升級，資料庫管理員經常需要匯入、匯出以及轉換資料。SQL Server 2000 中的資料轉換服務（DTS）為此提供了一組圖形化工具和可程式化對象，能夠協助管理員和開發人員解決從不同來源到單個或多個目標的資料轉移問題，包括資料提取、轉換以及合并。您可以將任務、工作流程動作和限制條件組成 DTS資料包，然後安排定期或在特定事件發生時執行該資料包。本白皮書將介紹 DTS，給出一些能夠用於建立 DTS 解決方案的組件和服務，介紹如何使用 DTS Designer 來實施 DTS 解決方案，最後將介紹 DTS 應用程式開發。

 

本頁內容
 DTS 簡介
 使用 DTS Designer
 儲存 DTS 資料包的選項
 將 DTS 作為應用程式開發平台

DTS 簡介
大部分公司的資料都具有多種儲存格式和儲存位置。為了支援決策制定、提高系統效能或升級現有系統，經常必須將資料從一個資料存放區位置轉移到另一個位置。

Microsoft SQL Server 2000 資料轉換服務（DTS）為此提供了一系列的工具，您可以用來從不同來源將資料提取、轉換和合并到單個或多個目標。通過使用DTS工具，您可以根據公司的特殊需求建立定製的資料移動解決方案，正如下面這些情形：

• 您已經在早期版本的 SQL Server 或其他平台（例如 Microsoft Access）上部署了一個資料庫應用程式。現在，新版本的應用程式需要 SQL Server 2000，而且需要更改資料庫結構描述，並轉換部分資料類型。
 
• 為了複製和轉換資料，可以構建一個 DTS 解決方案，將資料庫物件從未經處理資料源複製到 SQL Server 2000 資料庫中，同時重新設定資料欄並更改資料類型。您可以使用 DTS 工具來運行這個解決方案，或者將這個解決方案嵌入到您的應用程式中。
 
• 您必須將一些關鍵的 Microsoft Excel 試算表合并到 SQL Server 資料庫中。很多部門在每月底建立試算表，但是沒有設定排程來完成所有的試算表。
 
• 為了合并試算表資料，您可以構建一個 DTS 解決方案，使其在訊息被發送到訊息佇列時執行。這個訊息將觸發 DTS，使其從試算表中提取資料，執行各種定義的轉換，然後將資料裝載到 SQL Server 資料庫中。
 
• 您的資料倉儲中儲存了有關業務操作的曆史資料，您要使用 Microsoft SQL Server 2000 分析服務來匯總這些資料。這個資料倉儲需要每天夜間從聯機交易處理（OLTP）資料庫進行更新。而您的 OLTP 系統一天 24 小時都在使用中，其效能十分關鍵。

您可以構建一個 DTS 解決方案，使用檔案傳輸通訊協定（FTP）將資料檔案移動到本地磁碟機中，將資料裝載到事實表中，然後流量分析服務對資料進行統計。您可以安排這個 DTS 解決方案每天夜間執行，也可以使用新的 DTS 日誌選項來跟蹤這個過程所用的時間，使您能夠分析效能隨時間的變化。
 

DTS 是什麼?
DTS 是一組資料轉換工具，您可以用來在一個或多個資料來源（例如 Microsoft SQL Server、 Microsoft Excel 或 Microsoft Access）之間進行不同類型資料的匯入、匯出和轉換。其中的連通性通過資料訪問的開放式標準－OLE DB－來提供。ODBC（開放式資料庫連接）資料來源由 OLE DB Provider for ODBC 來支援。

您可以將 DTS 解決方案建立為一個或多個資料包。每個資料包中可以包含一組有序的任務，定義所要執行的工作，也可以包含資料和對象的轉換、定義任務執行的工作流程限制以及資料來源和目標的串連等。DTS 資料包也提供記錄資料包執行細節、控制事務以及處理全域變數等服務。

下列工具可以用於建立和執行 DTS 資料包：

• 匯入/匯出嚮導（Import/Export Wizard）用於構建相對簡單的 DTS 資料包，支援資料移轉和簡單轉換。
 
• DTS Designer 圖形化地實施 DTS 物件模型，允許您建立具有大量功能的 DTS 資料包。
 
• DTSRun是一個命令提示字元公用程式，用來執行已有的 DTS 資料包。
 
• DTSRunUI 是DTSRun的圖形化介面, 也允許傳遞全域變數和產生命令列。
 
• SQLAgent 不是一個 DTS 應用程式；但是 DTS 可以用它來安排資料包的執行。
 

您也可以使用 DTS 物件模型通過編程建立和運行資料包，構建定製任務以及構建定製轉換。

DTS 中的新內容?
Microsoft SQL Server 2000 引入了許多 DTS 增強和新特性：

• 新的 DTS 任務包括 FTP 任務、執行資料包任務、動態屬性任務以及訊息佇列任務。
 
• 增強日誌功能記錄了每個資料包執行的資訊，允許您擁有完整的執行曆史，並能查看任務中每個步驟的資訊。您可以產生異常檔案，包含可能由於錯誤而沒有執行的資料行。
 
• 您可以將 DTS 資料包儲存為 Microsoft Visual Basic 檔案。
 
• 新的多段資料泵允許進階使用者在不同階段定製資料轉換操作。同時，也可以使用全域變數作為查詢的輸入參數。
 
• 您可以在 DTS 轉換任務和執行 SQL 任務中使用參數化的源查詢。
 
• 您可以使用執行資料包任務，動態地將全域變數的取值從父資料包分配到子資料包。
 

返回頁首
使用 DTS Designer
DTS Designer 圖形化地實施 DTS 物件模型，允許您圖形化地建立 DTS 資料包。您可以使用 DTS Designer 來：

• 建立包含一個或多個步驟的簡單資料包。
 
• 建立包含複雜工作流程的資料包，這些工作流程中可包含使用有條件邏輯的多步操作、事件驅動的代碼或多個資料來源的串連。
 
• 編輯已有的資料包。
 

DTS Designer 介面由工作區域、工具列和菜單組成。其中工作區域用於構建資料包，工具列包含有資料包元素，您可以將它們拖動到設計頁中，菜單中包含有工作流程和資料包管理命令。

 

圖1：DTS Designer 介面
查看大圖。

在 DTS Designer 中，您可以將串連和任務拖動到設計頁中，並指定工作流程執行的順序，從而輕鬆地建立功能強大的 DTS 資料包。下面的內容將定義任務、工作流程、串連和轉換，並介紹如何使用 DTS Designer 輕鬆地實施 DTS 解決方案。

任務：定義資料包中的步驟
DTS 資料包中通常包含一個或多個步驟。每個任務定義了一個可能在資料包執行過程中執行的工作項目。您可以使用任務來：

• 轉換資料

 轉換資料任務
 用來將資料從來源移動到目標，可以選擇在資料上應用資料欄轉換。
 

 資料驅動的查詢任務
 用來對資料執行基於 Transact-SQL 的靈活操作，包括預存程序以及 INSERT、UPDATE 或 DELETE 聲明。
 
?
 平行資料泵任務 (1)
 僅可用於編程，平行資料泵任務執行與轉換資料和資料驅動的查詢任務相同的功能，但是支援 OLE DB 2.5 及更新版本所定義的 “章節式” 資料行集。
 
 
• 複製和管理資料

 批量插入任務
 用於快速地將批量資料裝載到 SQL Server 表或視圖中。
 

 Execute SQL 任務
 用於在資料包執行過程中運行 SQL 陳述式。Execute SQL 任務也可以儲存查詢的結果資料。
 

 Copy SQL Server Objects 任務
 用來將 SQL Server 物件從一個 SQL Server 安裝或執行個體中複製到另一個中。您可以複製資料和表等對象，也可以複製視圖和預存程序等對象定義。
 

 Transfer Database 任務(1)
 用來將 SQL Server 資料庫從一個 SQL Server 7.0 或 SQL Server 2000 執行個體移動或複製到 SQL Server 2000 執行個體中。
 

 Transfer Error Messages 任務(1)
 用於將使用者指定的錯誤訊息從 SQL Server 7.0 或 SQL Server 2000 執行個體複製到 SQL Server 2000 執行個體，這些錯誤訊息是由sp_addmessage系統預存程序所產生的。
 

 Transfer Logins 任務(1)
 用來將帳號從 SQL Server 7.0 或 SQL Server 2000 執行個體複製到 SQL Server 2000 執行個體。
 

 Transfer Jobs 任務(1)
 用來將作業從 SQL Server 7.0 或 SQL Server 2000 執行個體複製到 SQL Server 2000 執行個體。
 

 Transfer Master Stored Procedures 任務(1)
 用來將預存程序從 SQL Server 7.0 或 SQL Server 2000 執行個體中的master資料庫複寫到 SQL Server 2000 執行個體的 master資料庫。
 
 
• 從資料包中將任務作為作業運行

 ActiveX Script 任務
 用來編寫代碼，執行其他 DTS 任務中沒有的功能。
 

 Dynamic Properties 任務(1)
 用於在資料包運行時從 DTS 資料包外的來源處獲得數值，並將這些值分配給選定的資料包屬性。
 

 Execute Package 任務(1)
 用於從資料包中運行其他 DTS 資料包。
 

 Execute Process 任務
 用於運行可執行程式或批次檔。
 

 FTP 任務 (1)
 用來從遠程伺服器或 Internet 位置下載資料檔案。
 

 Message Queue 任務(1)
 用來從 Microsoft 訊息佇列發送和接受訊息。
 

 Send Mail 任務
 用來寄送電子郵件。
 

 Analysis Services Processing 任務 (2)
 用於處理一個或多個 SQL Server 2000 分析服務中所定義的對象。
 

 Data Mining 任務 (1,2)
 用於從 SQL Server 2000 分析服務所定義的資料採礦模型對象中建立一個預測查詢和輸出表。
 
 

1 SQL Server 2000中新增。

2 僅適用於已安裝 SQL Server 2000 分析服務的情形。

您可以程式化地建立定製任務，然後使用 Register Custom Task（註冊定製任務）命令將它們整合到 DTS Designer 中。

為了說明這些任務的使用，在這裡我們給出了一個包含兩個任務的簡單 DTS 資料包： Microsoft ActiveX Script 任務和 Send Mail 任務：

圖2：具有兩個任務的 DTS 資料包

ActiveX Script 任務可以駐留任何 ActiveX 指令碼引擎，包括 Microsoft Visual Basic Scripting Edition (VBScript)、Microsoft JScript 或者 ActiveState ActivePerl（您可以從http://www.activestate.com/下載）。 Send Mail 任務可以發送訊息，指出該資料包已經運行。請注意，這些任務是沒有順序的。在執行資料包時，ActiveX Script 任務和 Send Mail 任務同時運行。

工作流程：設定任務優先順序
當您在定義一組任務時，通常任務的執行是應該有一定順序的。如果這些任務擁有一定的順序，那麼每個任務將稱為一個過程中的一個步驟。在 DTS Designer 中，您可以在 DTS Designer 設計頁中對任務進行操作，使用優先順序限制來控制任務執行的順序。

優先順序限制將資料包中的任務依次連結起來。下表給出了您可以在 DTS 中使用的優先順序限制的類型。

優先順序限制 說明

On Completion（完成後）
(藍色箭頭)
 如果您希望任務 2 處於等待狀態，直至任務 1 完成（無論結果如何），那麼就使用 On Completion 優先順序限制將任務 1 連結到任務 2。
 

On Success（成功後）
(綠色箭頭)
 如果您希望任務 2 處於等待狀態，直至任務 1 成功完成，那麼就使用 On Success 優先順序限制將任務 1 連結到任務 2。
 

On Failure（失敗後）
(紅色箭頭)
 如果您希望任務 2 僅在任務 1 無法成功執行時才開始執行，那麼就使用 On Failure 優先順序限制將任務 1 連結到任務 2。
 

下圖給出了具有 On Completion 優先順序限制的 ActiveX Script 任務和 Send Mail 任務。當 ActiveX Script 任務完成後（不論成功還是失敗），Send Mail 任務都開始運行。

圖3：具有 On Completion 優先順序限制的 ActiveX Script 任務和 Send Mail 任務

您可以配置不同的 Send Mail 任務，一個用於 On Success 限制，另一個用於 On Failure 限制。這兩個 Send Mail 任務可以根據 ActiveX Script 的成功或失敗來發送不同的郵件。

圖4：郵件任務

您也可以在一個任務上應用多個優先順序限制。例如， Send Mail 任務"Admin Notification"可以具有來自指令碼#1的 On Success 限制和來自指令碼#2 的 On Failure 限制。在這種情況下，DTS 認為其使用邏輯"AND"關係。因此為了發送 Admin Notification 郵件，指令碼#1必須成功執行，而指令碼#2 必須失敗。

圖5：同一任務多個優先順序限制的樣本

串連：訪問和移動資料

為了成功地執行複製和轉換資料的 DTS 任務，DTS 資料包必須與其來源和目標之間建立有效串連，同樣需要串連到其他資料來源（例如查詢表）

在建立資料包時，您可以從有效 OLE DB 供應商和 ODBC 驅動程式列表中選擇連線類型，對串連進行配置。可用的連線類型包括：

• Microsoft 資料訪問組件(MDAC)驅動程式

 Microsoft OLE DB Provider for SQL Server
 

 Microsoft 資料連結
 

 Microsoft ODBC Driver for Oracle
 
 
• Microsoft Jet 驅動程式

 dBase 5
 

 Microsoft Access
 

 HTML 檔案（來源）
 

 Microsoft Excel 97-2000
 

 Paradox 5.X
 
 
• 其他驅動程式

 文字檔（來源）
 

 文字檔（目標）
 

 其他串連
 
 

DTS 允許您使用任何 OLE DB 串連。串連工具列中的表徵圖為常用串連提供了方便的訪問方式。

下圖介紹了一個具有兩個串連的資料包。資料被從一個 Access 資料庫（來源串連）複製到 SQL Server 生產資料庫（目標串連）。

圖6：具有兩個串連的資料包樣本
查看大圖。

這個資料包的第一步是一個執行 SQL 任務，該任務檢查是否已經存在目標表。如果已經存在，這個表將被刪除並重新建立。在成功的完成了執行 SQL 任務後，資料在第二步中被複製到 SQL Server 資料庫。如果複製操作失敗，則在第三步中發送一封電子郵件。

資料泵：轉換資料

DTS 資料泵是一個 DTS 對象，用來驅動資料的匯入、匯出和轉換。在轉換資料、資料驅動的查詢以及平行資料泵任務中將使用這個資料泵。這些任務將在來源和目標串連中建立資料行組，然後建立資料泵執行個體，將資料行在來源和目標之間移動。在資料行被複製時，對每一行進行資料轉換。

下圖的步驟 2 中，在 Access DB 任務和 SQL Production DB 任務之間使用了一個轉換資料任務。轉換資料任務是兩個串連之間的灰色箭頭。

圖7：轉換資料任務的樣本
查看大圖。

為了定義從來源串連收集到的資料，您可以為這個轉換任務建立一個查詢。DTS 支援參數化的查詢，允許您在查詢執行時定義查詢值。

您可以在該任務的屬性對話方塊中鍵入這個查詢。或者使用資料轉換服務查詢設計工具（Data Transformation Services Query Designer），該工具可以用來為 DTS 任務圖形化地建立查詢。下圖中，使用查詢設計工具構建了一個將三個表加入到pubs資料庫中的查詢。

圖8：資料轉換服務查詢設計工具介面
查看大圖。

在轉換任務中，您也可以定義對資料做出的更改。下表解釋了 DTS 提供的內建轉換功能。

轉換 說明
複製資料欄
 用來直接將資料從來源複製到目標資料欄中，對資料不進行任何轉換。
 
ActiveX 指令碼
 用來構建定製的轉換。請注意，由於轉換是逐行進行的，因此ActiveX 指令碼可能會影響 DTS 資料包的執行速度。
 
日期事件字串
 用來將來來源資料欄中的日期或事件轉換為目標資料欄中不同的格式。
 
小寫字母字串
 用來將來來源資料欄中的小寫字母轉換（如果需要）為目標資料欄的資料類型。
 
大寫字母字串
 用來將來來源資料欄中的所有大寫字母轉換（如果需要）為目標資料欄的資料類型。
 
字串中段
 用來從來來源資料欄中提取子字串，將其轉換，然後將結果複製到目標資料欄中。
 
修剪字串
 用於刪除來來源資料欄中字串前、後和中間的空白，並將結果複製到目標資料欄中。
 
讀取檔案
 用來開啟來來源資料欄中所指定的檔案的內容，並將其內容複寫到目標資料欄中。
 
寫入檔案
 用來將來來源資料欄（資料）的內容複寫到檔案中，該檔案的路徑由第二個來來源資料欄（檔案名稱）指定。
 

您也可以通過編程建立自己的定製轉換。建立定製轉換的最快方法是使用Active Template Library（Active Template Library，ATL）定製轉換模板，該模板包含在 SQL Server 2000 DTS 樣本程式中。

資料泵錯誤記錄檔
SQL Server 2000中 擁有一種記錄轉換錯誤的新方法。您可以定義三種異常記錄檔，用於資料包執行過程：錯誤文字檔、來源錯誤資料行檔案以及目標錯誤資料行檔案。

• 常規錯誤資訊被寫入到錯誤文字檔中。
 
• 如果轉換過程失敗，那麼來來源資料行將出現錯誤，並將該行寫入到來源錯誤資料行檔案中。
 
• 如果插入過程失敗，那麼目標資料行將出現錯誤，並將該行寫入到目標錯誤資料行檔案中。
 

異常記錄檔被定義在轉換資料的任務中。每個轉換任務可以擁有它自己的記錄檔。

資料泵階段

在預設情況下，資料泵只有一個階段：資料行轉換。這個階段就是您所配置的在轉換資料任務、資料驅動的查詢任務以及平行資料泵任務中的資料欄轉換，而不選擇階段。

多資料泵階段功能是 SQL Server 2000 中所新增的。通過在 SQL Server Enterprise Manager 中選中多段資料泵選項，您可以在操作過程中的不同地方訪問資料泵，添加功能。

在將一行資料從來源複製到目標時，資料泵按照下圖所示的基本程式進行操作。

圖9：資料泵過程
查看大圖。

在資料泵處理完最後一行資料後，任務完成，資料泵操作結束。

如果進階使用者需要在資料包中添加功能，使其支援任何資料泵階段，他可以這樣做：

• 為每個定製的資料泵階段編寫一個ActiveX指令碼階段。如果您使用ActiveX指令碼功能來定製資料泵階段，不需要任何資料包以外的代碼。
 
• 在 Microsoft Visual C++ 中建立 COM 物件，定製所選中的資料泵階段。您在資料包以外開發這個程式，轉換的每個所選中的階段都將調用這個程式。與訪問資料泵階段的 ActieX 指令碼方法不同的是，ActiveX 指令碼方法為每個選中的階段使用不同的功能和進入點，而這種方法提供了單一進入點，由多個資料泵階段在任務執行過程中調用。
 

返回頁首
儲存 DTS 資料包的選項
下列選項可以儲存 DTS 資料包：

• Microsoft SQL Server

如果您希望在任何網路中的 SQL Server 執行個體中儲存資料包，請將您的 DTS 資料包儲存在 Microsoft SQL Server 上，並保留這些資料包的清單，在資料包開發過程中添加和刪除資料包版本。
 
• SQL Server 2000 中繼資料服務

如果您計劃跟蹤資料包版本、中繼資料和資料血統資訊，請將 DTS 資料包儲存在中繼資料服務上。
 
• 結構化的隱藏檔

如果您需要在網路中複製、移動和發送資料包，而不想把資料包儲存到 Microsoft SQL Server 資料庫中，請將 DTS 資料包儲存為結構化的隱藏檔。
 
• Microsoft Visual Basic

如果您希望將其整合到 Visual Basic 程式中，或作為 DTS 應用程式開發的原型，請將由 DTS Designer 和 DTS 匯入/匯出嚮導建立的 DTS 資料包儲存為 Microsoft Visual Basic 檔案。
 

返回頁首
將 DTS 作為應用程式開發平台
DTS Designer 提供了多種資料移動任務的解決方案。DTS 通過提供對 DTS 物件模型的程式化訪問，擴充了許多可用的解決方案。使用Microsoft Visual Basic、Microsoft Visual C++ 或其他支援 COM 的應用程式開發系統 ，您可以為您的環境開發一個定製的 DTS 解決方案，使用圖形工具中所不支援的功能。

DTS 以多種不同的方式為開發人員提供支援：

• 構建資料包

您可以開發極其複雜的資料包，訪問物件模型中的所有功能，而不需要使用 DTS Designer 或 DTS 匯入/匯出嚮導。
 
• 擴充資料包

您可以通過定製的任務和轉換來增加新的功能，這些任務和轉換是專門為您的業務定製的，並且能在DTS中重複使用。
 
• 執行資料包

DTS 資料包的執行不需要基於任何所提供的工具，可以通過 COM 事件通過編程執行 DTS 資料包和顯示過程，允許構建嵌入式或定製的 DTS 執行環境。
 

所提供的 DTS 程式樣本能夠協助您瞭解 DTS 編程。這些樣本與 SQL Server 2000 一同安裝。

如果您要開發 DTS 應用程式，那麼您可以重新分配 DTS 檔案。更多資訊，請參考 SQL Server 2000 光碟片的 Redist.txt 檔案。

更多資訊
Microsoft SQL Server 2000 Books Online中擁有許多有關 DTS、使用DTS應用程式以及構建定製解決方案的資訊。其他資訊，請參考下列資源：

• Microsoft SQL Server Web網站，地址為http://www.microsoft.com/china/sql/。
 
• Microsoft SQL Server Developer Center，地址為http://msdn.microsoft.com/library/default.asp?URL=/sqlserver/。
 
• SQL Server Magazine，地址為http://www.sqlmag.com/。
 
• Microsoft.public.sqlserver.server和 microsoft.public.sqlserver.datawarehouse新聞群組，地址為 news://news.microsoft.com。
 
• 關於SQL Server的微軟官方課程（Microsoft Official Curriculum）。如需瞭解最新的課程資訊，請訪問Microsoft Training and Services Web網站，地址為http://www.microsoft.com/traincert/default.asp
 

本文檔所提供的資訊資料僅代表Microsoft公司在資訊發布當日就研討活動所圍繞的問題持有的臨時觀點。鑒於Microsoft公司必須針對瞬息萬變的市場狀況不斷做出相應調整，故而，本文檔內容不應被解釋為Microsoft方面所做出的任何承諾，與此同時，Microsoft也無法在發布之日後繼續保證檔案所含資訊的準確性。

本白皮書僅供用於資訊參考目的。Microsoft並未在本文檔中提供任何形式的保證、明示或暗示。

遵守所有適用著作權法律是文檔使用者所應承擔的義務。Microsoft公司雖未在著作權保護下就與本文檔相關的權利做出任何限定，但是，任何人未經Microsoft公司書面授權許可，均不得出於任何目的、以任何形式、利用任何手段（電子、機械、影印、錄音等）將本文檔的任何組成部分製作成拷貝、儲存或引入檢索系統、亦或向任何對象進行傳送。

Microsoft公司可能就本文檔所涉及的主題擁有專利、專利申請、商標、著作權或其它形式的智慧財產權。除非已同Microsoft公司簽訂書面許可協議，並根據協議條款獲得明確授權，任何出示本文檔的行為均無法使您具備針對上述專利、商標、著作權或其它智慧財產權加以利用的許可許可權。

?2000 Microsoft Corporation. 保留所有權利.

Microsoft、ActiveX、JScript、Visual Basic和Visual C++ 均系Microsoft公司在美國和/或其它國家所擁有的註冊商標或商標。

本文檔所涉及的其它公司和產品的真實名稱均為其各自所有者持有的商標。