一步一步學習BI（3）-Integration Services建立ETL包

Microsoft Integration Services 是一個可以產生高效能資料整合解決方案（包括為資料倉儲擷取、轉換和下載 (ETL) 包）的平台。

Integration Services 包括用於產生和調式包的圖形工具和嚮導；用於執行工作流程函數（如 FTP 操作）、執行 SQL 陳述式以及寄送電子郵件的任務；用於提取和載入資料的資料來源和目標；用於清除、彙總、合并和複製資料的轉換；用於管理組件執行和儲存的管理服務，即 Integration Services；以及用於 Integration Services 物件模型編程的API (API)。

 

說明：

本文是作者在學習Integration Services過程中的筆記，基本思路和內容都來自SQL Server 聯機叢書。覺得園子裡的BI方面的資源比較少，就放上來了。

學習本文需要建立在對Integration Services基本瞭解的基礎。

如果沒有任何瞭解，請參考一步一步學習BI（1）-認識Integration Services 

本文中將用到的資料庫為微軟的AdventureWorksDW 

本文中將用到的文本資料在這裡 

請先下載這兩份資料檔案，我們在接來實驗中將要用到。 

 

目標：

在本文中我們將學習如何使用 SSIS 設計器建立一個簡單的 Microsoft SQL Server Integration Services 包。所建立的包將從一般檔案提取資料，重新設定資料的格式，然後將已重新設定格式的資料插入到事實資料表中並將擴充包以闡釋迴圈、包配置、日誌記錄和錯誤流。

 

步驟：

1. 建立項目和基本包

   • 這一小節中我們將建立一個簡單的ETL包，該包可以從單個一般檔案源（TXT檔案）提取資料，使用兩個尋找轉換組建轉換該資料，然後將該資料寫入AdventureWorksDW中的FactCurrencyRate事實資料表中。

     1. 建立一個Integration Services Project, 在建立的時候Project的時候取名為SSISTutorial。將預設的包檔案Package.dtsx重新命名為Lesson1.dtsx。

     2. 按右鍵工作區的下方的Conection Managers，在右鍵菜單中選擇“New Flat File Connection”

     3. 在彈出Flat File Connection Manager Editor中Connection manager name取名為Sample Flat File Source Data。單擊Brower,在開啟的對話方塊中選擇SampleCurrencyData.txt檔案。

     4. 在 Flat File Connection Manager Editor中選擇Advanced(進階)選項，配置每一行的屬性。

        • 將 Column 0 名稱屬性改為 AverageRate

        • 將 Column 1 名稱屬性改為 CurrencyID

        • 將 Column 2 名稱屬性改為 CurrencyDate

        • 將 Column 3 名稱屬性改為 EndOfDayRate

        • 點擊Suggest Types（建議類型）。Integration Services 將根據前 100 行資料自動建議最合適的資料類型。在彈出的視窗中不需要做任何更改，點擊OK(確定)將擷取由Integration Services建議的資料類型，如果點擊Cancle(取消)，則不對列中繼資料提供任何建議，並使用預設字串 (DT_STR) 資料類型。

        • 但是因為 CurrencyID 和 CurrencyDate 列建議的資料類型與目標表中的欄位的資料類型不相容。由於 DimCurrency.CurrencyAlternateKey 的資料類型為 nchar (3)，CurrencyID 必須從字串類型 [DT_STR] 改為字串類型 [DT_WSTR]。另外，欄位 DimTime.FullDateAlternateKey 被定義為 DataTime 資料類型，因此 CurrencyDate 需要從日期類型 [DT_Date] 改為資料庫時間戳記類型 [DT_DBTIMESTAMP]。所以我們需要在“屬性”窗格中，將列 CurrencyID 的資料類型從字串類型 [DT_STR] 改為 Unicode 字串類型 [DT_WSTR]，將列 CurrencyDate 的資料類型從日期類型 [DT_DATE] 改為資料庫時間戳記類型 [DT_DBTIMESTAMP]，最後確定。

     5. 按右鍵工作區的下方的Conection Managers，在右鍵菜單中選擇“New OLE DB Connection”, 在彈出的Configure OLE DB Connection Manager快顯視窗中選擇建立，在Connection Manager視窗中，Sever name本地，驗證方式為Windows驗證，資料庫選擇AdventureWorksDW，最後確定。  

     6. 在主工作視窗上方點擊“控制流程”選項卡，並從左邊的工具箱中拖拽一個“資料流程工作”到控制流程設計面板上，命名為：Extract Sample Currency Data。註：好的做法是為添加到設計介面的所有組件提供唯一的名稱。考慮到易用性和可維護性，名稱應說明每個組件執行的功能。這個與編程開發中是一致的。

     7. 雙擊剛剛建立的資料流程工作“Extract Sample Currency Data” 進入到資料流設計面板，從左邊的工具箱中拖拽一個“Flat File Source” 到設計面板中並重新命名為“Extract Sample Currency Data”， 雙擊“Flat File Source” 在彈出的編輯視窗中為Flat File connection manager選擇我們之前建立的“Sample Flat File Source Data”, 確定。

     8. 配置了用於從源檔案提取資料的一般檔案源後，下一個任務是定義擷取 CurrencyKey 和 TimeKey 的值所需的尋找轉換。尋找轉換通過將指定輸入列中的資料連線到引用資料集中的列來執行尋找。引用資料集可以是現有的表或視圖，也可以是新表或 SQL 陳述式的結果。在這裡，尋找轉換使用 OLE DB 連線管理員串連到包含引用資料集的來源資料的資料庫。我們將向包中添加以下兩個尋找轉換組件並對其進行配置：

        • 一個轉換是根據一般檔案中匹配的 CurrencyID 列值對 DimCurrency 維度資料表的 CurrencyKey 列中的值執行尋找。
        • 一個轉換是根據一般檔案中匹配的 CurrencyDate 列值對 DimTime 維度資料表的 TimeKey 列中的值執行尋找。
     9. 從工具向中拖拽一個Lookup(尋找)組建到資料流設計表單中命名為Lookup Currency Key，將Extract Sample Currency Data 的輸出作為Lookup Currency Key的輸入將兩個組件連結起來。雙擊Lookup Currency Key，在General(常規)中Cache Mode預設為Full cache, Connection type預設為OLE DB connection manager.在Connection選項中，OLE DB connection manager選擇之前建立好的“LocalHost.AdventureWorksDW”, 選擇Use results of an SQL query:

        select * from (select * from [dbo].[DimCurrency]) as refTablewhere [refTable].[CurrencyAlternateKey] = 'ARS'OR[refTable].[CurrencyAlternateKey] = 'AUD'OR[refTable].[CurrencyAlternateKey] = 'BRL'OR[refTable].[CurrencyAlternateKey] = 'CAD'OR[refTable].[CurrencyAlternateKey] = 'CNY'OR[refTable].[CurrencyAlternateKey] = 'DEM'OR[refTable].[CurrencyAlternateKey] = 'EUR'OR[refTable].[CurrencyAlternateKey] = 'FRF'OR[refTable].[CurrencyAlternateKey] = 'GBP'OR[refTable].[CurrencyAlternateKey] = 'JPY'OR[refTable].[CurrencyAlternateKey] = 'MXN'OR[refTable].[CurrencyAlternateKey] = 'SAR'OR[refTable].[CurrencyAlternateKey] = 'USD'OR[refTable].[CurrencyAlternateKey] = 'VEB'

2. 在Columns(列)選項卡中，將Available Input Columns面板中的CurrencyID拖拽到Available Lookup Columns中的CurrencyAlternateKey上，並將CurrencyKey的複選框選上。
4.   從工具向中拖拽一個Lookup(尋找)組建到資料流設計表單中命名為Lookup DateKey。編輯Lookup DateKey,在串連選項上的“OLE DB 連線管理員”對話方塊中，確保顯示 localhost.AdventureWorksDW。在“使用表和視圖”框中，選擇[dbo].[DimTime]。在Columns(列)選項卡中，將Available Input Columns面板中的CurrencyDate拖拽到Available Lookup Columns中的FullDateAlternateKey上，並將TimeKey的複選框選上。將Lookup Currency Key的輸出作為Lookup DateKey的輸入，在彈出框中的選擇類型中選擇Lookup Match Output.
5. 在ToolBox中，展開Data Flow Destinations，將OLE DB Destination拖拽到Lookup DateKey下面，命名為Sample OLE DB Destination. 將Lookup DateKey的輸出作為Sample OLE DB Destination的輸入，將元件連線起來，在在彈出框中的選擇類型中選擇Lookup Match Output.雙擊Sample OLE DB Destination，確定OLE DB connection manager的串連為LocalHost.AdventureWorkDW. 在Name of the table or the View框中選擇[dbo].[FactCurrencyRate]。單擊左邊的Mappings,驗證 AverageRate、CurrencyKey、EndOfDayRate 以及 TimeKey 輸入列是否已正確映射到目標列。如果映射了同名列，則說明映射正確。
6.   
7. 執行Lesson1包，正確執行，我們發現共有1097條資料按照我們的要求匯入到了目標資料庫中

添加迴圈

• 在上一節中我們建立了一個基本包，建立了從單個一般檔案源提取資料的包，然後使用尋找轉換功能對資料進行了轉換，最後將資料載入到AdventureWorksDW樣本資料庫的FactCurrencyRate事實資料表中。但是提取，轉換和載入（ETL）過程很少使用單個一般檔案。典型的ETL過程從多個一般檔案源提取資料。從多個源提取資料需要採用迭代控制流程。Microsoft Integration Services最可能出現的功能之一就是方便快捷地向包中添加迭代或迴圈。Integration Services為迴圈遍曆包提供了兩種容器類型：Foreach迴圈容器和For迴圈容器。Foreach迴圈容器使用列舉程式執行迴圈，而For迴圈通常使用Variant 運算式。

• Foreach 迴圈容器使包能夠對指定列舉程式的每個成員重複執行控制流程。使用Foreach 迴圈容器，可以枚舉：

  • ADO記錄集行和架構資訊

  • 檔案和目錄結構

  • 系統，包和使用者變數

  • SQL Server管理對象（SMO）

•  

  1. 開啟上一小節的工程檔案，選中Lesson1.dtsx 包檔案，複製這個包檔案，選擇SSISTutorial後粘貼。將複製過來的Lesson1.dtsx 重新命名為Lesson2.dtsx。這一小節中我們將在Lesson2.dtsx 這個包檔案中進行開發。

  2. 從ToolBox中拖拽組件Foreach Loop Container 到控制流程面板上，命名為Foreach File in Folder後雙擊開啟。在Foreach Loop Editor的Collection中選擇Foreach File Enumerator。在Enumerator configuration中選擇一個檔案夾，這裡我們選擇準備好的Sample Data檔案夾，Files填寫為Currency_*.txt。

  4. 從左邊選擇Variable Mappings(變數映射)，在Variable列中選擇 New Variable 。在彈出的Add Variable對話方塊中Name 輸入 varFileName。確定Add Variable 對話方塊，確定Foreach Loop Editor.

  6. 將 Extract Sample Currency Data 資料流程工作拖動到現已重新命名為 Foreach File in Folder 的 Foreach 迴圈容器中,如所示。

  8. 在Connection Managers 處選擇先前建立好的Sample Flat File Source Data 一般檔案串連，按右鍵 Sample Flat File Source Data，再選擇Properties.在Properties視窗中的Expressions窗格中，點擊省略符號，開啟Property Expressions Editor視窗，在Property 列的下拉式功能表中選擇ConnectionString.
  10. 點擊Expressions列的省略符號按鈕，開啟Expression Builder視窗，在視窗的左上將Varibales展開，將VarFileName::Variable拖拽到Expression視窗中。確定開啟的視窗回到控制流程工作面板。
  12. 又是一次激動人心的時刻到了，執行Lesson2.dtsx，按照我們的設想路徑下的所有檔案都按規則被匯入到目標資料庫中。趕快檢查資料庫去吧，或者從輸出視窗中也可以看到迴圈輸出成功的資訊。

添加包配置

• 包配置允許從開發環境的外部設定運行時屬性和變數。有了配置我們就能開發靈活且易於部署和分發的包。Microsoft Integration Services提供了以下配置類型：

  • XML設定檔

  • 環境變數

  • 登錄機碼

  • 父包變數

  • SQL Server表

• 使用包設定精靈，將建立一個XML配置，以便通過使用映射到Directory屬性的包層級變數來更新Foreach迴圈容器的Directory 屬性。在建立設定檔後，將從開發環境的外部修改該變數的值，並將修改後的屬性指向新的樣本資料檔案夾。再次運行包時，設定檔將填充該變數的值，而該變數又會更新Directory屬性。結果，包將迭代遍曆新資料檔案夾中的檔案，而不是迭代遍曆在該包中硬式編碼原始檔案夾中的檔案。

  1. 將Lesson2.dtsx 複製，粘貼，重新命名為Lesson3.dtsx，這一小節將在Lesson3.dtsx這個包上做開發。

  2. 首先建立映射到Directory屬性的新的包層級的變數。既然是包層級的就是要先選擇我麼要操作的包。在Lesson3.dtsx包的“Control Flow” 面板的選項卡上單擊，這會將要建立的變數的範圍設定為包。

  3. 在 SSIS 菜單中，選擇Variables調出側邊欄，新增一個變數varFolderName，資料類型為String。

  5. 回到Control Flow工作面板，雙擊Foreach File in Folder容器，在Foreach Loop Editor的Collection中的Expressions添加了我們剛改添加的包層級的變數。確定後回到控制流程面板。

  7. 在SSIS菜單上選擇Package Configurations…,在彈出的視窗中首先Enable package configurations,然後增加一個包配置。在包設定精靈中Next，在Configuration file name中Browse到工程目錄，輸入SSISTutorial作為檔案名稱。Next, 在Slect Properties to Export的Objects中展開Variables,展開varFolderName,展開Properties,選擇Value。Next, 在Completing the Wizard中Configuration name 為SSIS Tutorial Directory configuration，完成。

  8. 到剛才儲存的工程目錄下找到SSISTutorial.dtsConfig檔案，這個一個xml檔案，用記事本開啟，找到Configuration節點下的ConfiguredValue配置項，配置一個路徑。這樣比較Lesson2的包檔案，當我們要匯入不同檔案夾下的文字檔時只需要修改這個設定檔就可以了。而不是像Lesson2中那樣Hard Code在代碼中。

  9. 執行Lesson3.dtsx包，將會將所配置路徑下的文字檔迴圈按照規則匯入到目標資料庫中。

添加日誌記錄

• Microsoft Integration Services包含日誌記錄功能，這些功能可以使我們可以通過提供任務和容器事件跟蹤來對包執行疑難排解和監控。日誌記錄功能非常靈活，可以在包層級或者在包中的各個任務和容器上啟用。可以選擇要記錄的事件，也可以對單個包建立多個日誌。

• 日誌記錄由記錄提供者提供。每個記錄提供者可以將日誌記錄資訊寫入不同的格式和目標類型。Integration Services提供了以下記錄提供者：

  • 文字檔

  • SQL Server Profiler

  • Windows事件記錄

  • SQL Server

  • XML 檔案

• 在本節中我們將在上節的基礎上添加並配置日誌記錄，以在包執行過程中監控特定事件。

  1. 將Lesson3.dtsx 複製，粘貼，重新命名為Lesson4.dtsx，這一小節將在Lesson4.dtsx這個包上做開發。

  2. 在SSIS菜單上選擇Logging….,在彈出的配置視窗中Provider type選擇SSIS log provider for Text files. 然後單擊Add.【Integration Services 將向包中添加一個預設名稱為用於文字檔的 SSIS 記錄提供者的新文字檔記錄提供者。現在便可對新的記錄提供者進行配置。】，在名稱中填寫Lesson 4 Log File，在配置列中選擇建立，Usage Type選擇Create file，File點擊Browse將開啟工程目錄，輸入TutorialLog.log作文記錄檔名。

  4. 在Containers窗格中，展開包容器階層中的所有節點，然後清除包括 Extract Sample Currency Data 複選框在內的所有複選框。現在選中 Extract Sample Currency Data 複選框以僅擷取有關此節點的事件。在Details選項卡的Events列中，選擇 PipelineExecutionPlan 和 PipelineExecutionTrees 事件。

  6. 在Providers選項卡上的Name列中，選擇 Lesson 4 Log File。為包建立記錄提供者後，可以選擇取消選擇它以臨時關閉日誌記錄，而不必刪除後再重新建立記錄提供者。

  7. 執行Lesson4.dtsx包，到工程目錄下去查看我們的記錄檔吧。

添加錯誤留重新導向

• 為了處理在轉換過程中可能發生的錯誤，Microsoft Integration Services 使我們能夠基於每個組件和每個列來決定如何處理無法轉換的資料。可以選擇忽略某些列中的失敗，重新導向整個失敗的行或者只是使組建失敗。預設情況下，Integration Services中的所有組件被配置為在發生錯誤時失敗。而使組件失敗又會導致包失敗，並使所有後續處理停止。

• 如果不讓失敗導致包停止執行，一個好的方法是通過配置使在轉換中發生潛在處理錯誤是這些錯誤能夠得到處理。雖然可能選擇忽略失敗以確保包陳宮運行，但通常更好的做法是將失敗的行重新導向到另一個處理路徑，在這裡可以使資料和錯誤持久化，接受檢查並在隨後的某個時間對其進行重新處理。

• 在本節的開發包中，將建立一個執行個體資料檔案的損壞版本，損壞的檔案將在運行包時強制發生處理錯誤。為了處理錯誤資料，我們將添加並配置一個一般檔案目標，它會將所有無法在Lookup Currency Key轉換中找到尋找值的行寫入檔案。

• 將錯誤資料寫入檔案之前，需要包括一個使用指令碼擷取錯誤說明的指令碼組件。然後，將重新設定Lookup Currency Key轉換，以便將所有無法處理的資料重新導向到指令碼轉換中。

1. 將Lesson4.dtsx 複製，粘貼，重新命名為Lesson5.dtsx，這一小節將在Lesson5.dtsx這個包上做開發。

2. 建立損壞的文字檔包。在本工程的下載檔案中有個Sample Data2檔案夾，我們將開啟其中的 Currency_VEB.txt 檔案，並使用文字編輯器的尋找和替換功能，尋找 VEB 的所有執行個體，並替換為 BAD。在包含其他樣本資料檔案的同一檔案夾中，將修改後的檔案另存新檔 Currency_BAD.txt儲存在Sample Data2檔案夾中。

3. 刪除工程目錄下的記錄檔，執行一下Lesson5.dtsx，看看是否有錯誤資訊。Error: 0xC020901E at Extract Sample Currency Data, Lookup Currency Key [26]: Row yielded no match during lookup.

4. 在Data Flow面板上從Data Flow Transformations中拖拽一個Script Component組件到面板上，Lookup Currency Key的右下方，在彈出的Type選擇上選擇Transgormation。將Lookup Currency Key的紅色箭頭拖拽至這個組件上，在彈出的Configure Error Output中將Error列的類型選擇為Redirect row。

6. 重新命名群組件為Get Error Description，雙擊Get Error Description，在Script Transformation Editor中的Input Columns選項，Input Column 選擇ErrorCode列。

8. 在Inputs and Outputs選項上，展開Output 0, 展開Output Columns，點擊Add Column，取名為ErrorDescription，並設定資料類型為string [DT_WSTR]。

9.  

10. 選擇Script 選項，單擊Edit Script…, 開啟ssisscript，重寫其中的方法後確定。

    public override void Input0_ProcessInputRow(Input0Buffer Row)    {        Row.ErrorDescription = this.ComponentMetaData.GetErrorDescription(Row.ErrorCode);    }

11. 回到Data Flow工作面板，為Get Error Description添加一個平面目標檔案作為錯誤記錄檔的輸出記錄。從Toolbox中拖拽一個Flat File Destination到Get Error Description下方並命名為Failed Rows。雙擊開啟Flat File Destination Editor，New…一個Flat File connection manager,在快顯視窗的類型選擇中選擇Delimited。Conection manager name 中命名為Error Data，Browser 將開啟工程所在的檔案路徑，取檔案名稱為ErrorOutput.txt。回到Flat File Destination Editor中將Overwrite data in the file前面的勾取消。點擊Mappings，確認所有的列都是正確的對應上了。

12. 執行包Lesson5,包將被正確執行，錯誤資料將記錄到ErrorOutput.txt檔案中，趕快看看去吧。

 

終於是寫完了，有點長和有點羅嗦。

希望本文對你有所協助。

本文完整工程檔案下載為：SSISTutorial.zip