SQL Server BI Step by step 5 事務，錯誤輸出，事件處理，日誌記錄

 

SQL Server BI Step by Step SSIS 5 (End)
                                                --- 事務，錯誤輸出，事件處理，日誌記錄

 

和其它程式一樣，SSIS包同樣需要健壯，穩定的運行，這樣的程式才有可靠性和延展性。SSIS提供了如下方面的支援: 

1.事務: 可以對一個程式包設定成一個或者多個事務，甚至可以對兩個程式包設定成一個事務。為了保證資料的一致性，你還可以DTC事務或者SQL Server引擎級的事務。
2.檢查點: 用來記錄一個程式包出錯時任務的運行情況，以便程式包再次啟動時，直接從發生錯誤的任務直接執行.
3.錯誤輸出:即使再完美的程式也會有錯誤，尤其對於資料流中的任務來說，及有可能由于格式，類型等問題，導致這一行的發生錯誤。SSIS允許針對這樣的每行進行錯誤輸出處理，你可 以進行修複或者記錄，當然你也可以忽略。 
4.優先順序條件約束:在控制流程中，你不僅可以使用一個任務的成功，失敗或執行結束作為條件來執行下面的任務，甚至可以使用運算式來做為條件。
5.事件處理: 在SSIS包中，事件處理是和控制流程，資料流相併列的。在這裡，你可以對程式包，任務或者容器的不同的事件進行處理，事件還可以用於設定斷點並控制日誌記錄。我們可以使用OnError事件來捕獲錯誤.
6.日誌記錄: 可以在運行時，記錄指定的事件資訊，可以儲存在本地文本或者XML檔案中，也可以儲存到資料庫中，或者是Windows EventLog，Profiler檔案中，甚至你可以擴充自己的日誌記錄。

需求:
     為了和其它系統整合，AdventureWorks系統的產品價格即時更新，需要從一個指定的URL下載一個壓縮檔，解壓後，對這個資料檔案進行分析匯入。由於下載的檔案是由一個非程式維護的檔案，由於人為因素，裡面的資料格式有可能有錯誤，但是要求忽略這樣的資料。另外，如果處理失敗，需要對保留曆史檔案。如果發生錯誤，需要發送錯誤Email.同時需要保留程式日誌，以便尋找錯誤原因
實現步驟：

------------------------------------------------------------下載解壓檔案--------------------------------------

一、 下載解壓檔案
1. 在你的N盤下面建立一個ProductPrice檔案夾來存放壓縮和資料檔案，在下面建立一個bak目錄存放備份檔案。
2.   建立一個SSIS包，同時建立以下變數：

 

• 如何設定變數的屬性：
  參考：http://msdn.microsoft.com/zh-cn/library/ms141663.aspx
  選擇需要設定的變數，點擊屬性欄視窗進行設定。

3. 建立一個OLE DB串連，串連我們的AdventureWorks資料庫.
4. 建立一個Http串連,URL填寫我們的遠端壓縮檔的位置（當然，這裡也可以使用變數). 
 
  

當然，實際中這裡可能需要使用憑據或者認證。

 

5. 在控制流程中添加指令碼任務，用來完成我們下載檔案，設定讀變數User::zipPath。
 
編輯指令碼:

指令碼

public void Main() 
{    
      // TODO: Add your code here
      object  nativeOjbect =  Dts.Connections[0].AcquireConnection(null);     

      HttpClientConnection    connection = new HttpClientConnection(nativeOjbect);      

      string  filename = Dts.Variables["zipPath"].Value.ToString();     

      connection.DownloadFile(filename,true);      

     

      Dts.TaskResult = (int)ScriptResults.Success; 
}

執行程式包，你會發現已經能夠將rar檔案下載到本地的檔案夾中.

----------------------------------------------------解壓-------------------------------------------------------

 

二、 解壓檔案
1. 現在我們來完成解壓的任務，首先需要確認本地已經安裝了WinRar應用程式。
添加  執行進程任務  組件,命名為：解壓檔案。設定屬性WindowStyle屬性為Hidden,進行運算式設定，分別對屬性進行如下設定

:

執行程式包，c盤ProductPrice目錄下會同時出現兩個檔案，一個zip壓縮檔，和一個ProductList.txt檔案。

 

 

-----------------------------------對解壓檔案中資料進行分析，對資料庫進行update----------------------------

 

三、 對解壓檔案中資料進行分析，對資料庫進行update
1. 建立一個一般檔案連線管理員 ，命名為ProductList，指向我們的C:\SSIS_Example\ProductPrice \ProductList.txt檔案.

對一般檔案連線管理員進行編輯

切換到tab進階：
DataType設定為Unicode
但是，我們的檔案路徑是變數，不能夠寫死在這裡，所以在屬性裡對其Expressions設定，設定ConnectionString的值為@[User::dataPath]

 

2. 添加資料流程工作組件命名為”分析資料更新價格”.編輯進入資料流，
1) 首先添加一個一般檔案源組件，串連我們的剛才建立的檔案串連ProductList。檔案中只有兩個列，一個是產品編號，一個是產品價格。兩個都為DT_WSTR類型。
 

在tab列中對輸出資料行列明進行修改。

2) 添加資料轉換工作群組件，我們將產品價格轉換為小數類型:

• 配置錯誤輸出
  執行程式包，出錯了，錯誤就發產生我們的資料轉換任務，對於空的字串，不能夠將其轉換成數字類型，怎麼辦？點擊左下角的配置錯誤輸出，這裡我們可以指定行級錯誤，對出錯的行選擇:
        1.組件失敗: 導致整個任務執行失敗.
        2.忽略失敗: 忽略這個行的出錯，對於這個任務，忽略的行的PriceList的值為NULL
        3.重新定行: 失敗的資料，可以重新定義輸出.
  在這裡，我們選擇重新定行

同理，我們也可以對上面的檔案串連進行同樣的錯誤輸出處理，以防止在讀取檔案時，由於資料原因發生錯誤。

 

3) 添加一個OLE DB命令，完成資料的更新。SqlCommand為

指令碼

UPDATE   Production.Product  SET  ListPrice =? WHERE ProductNumber=?

 
在列映射欄裡，對兩個參數進行映射，注意前後的順序。 
 

4) 添加行計數任務，注意，從資料轉換任務的下面拖拉紅色的錯誤輸出到行計數下面，使我們出錯的行資料流入這個任務。設定行計數任務的變數為@errorcount.再次執行包，錯誤沒有了，探索資料轉換下面已經分成3行的正確走向，和2行的錯誤走向

運行：

 

運行前資料庫：

 
Update後資料庫

 

-----------------------------------------對壓縮包、備份、刪除資料檔案操作------------------------------------

四、 對壓縮包、備份、刪除資料檔案操作

1. 添加對檔案的處理。添加三個檔案系統執行到控制流程中，分析命名刪除壓縮檔，備份壓縮檔，刪除資料檔案。注意，均使用變數的方式（具體見下載源檔案).

• 刪除壓縮檔工作群組件設定
• 
   
• 設定控制流程
  我們希望在資料流執行失敗時執行備份壓縮檔，在這裡我們需要設定優先順序條件約束,預設的是綠色的完成時執行。配置完成後右擊串連，選擇失敗。
• 
   
•   刪除壓縮檔工作群組件設定

 

• 備份壓縮檔工作群組件設定
  這裡我們的變數中backPath使用了計算的變數來算出日期後動態改變value，這裡的變數設定，請參照一開始的如何設定變數的屬性！
• 
   

到此為止實現了刪除壓縮檔、備份壓縮檔的操作：

  

• 刪除資料檔案工作群組件設定
  不管執行成功還是失敗，我們都希望在最後刪除資料檔案，我們又將兩個檔案系統任務同時指向了刪除資料檔案任務。而我們需要設定他們之間任務一個執行完成時即執行，如上面一樣，右擊選擇編輯進入優先順序條件約束編輯器，我們選擇邏輯或，即兩個任務有一個執行成功即可
• 
   

 

-------------------------------------------------設定檢查點---------------------------------------------------

五、 設定檢查點（Checkpoings）

     雖著產品數量的增多，下載的壓縮包太大，每次執行失敗時，我們希望能夠從執行失敗的任務直接運行，而不用都要重新去下載和解壓.
     我們來設定Checkpoings，檢查點來完成這樣的功能。

1) 首先在控制流程tab中設定Package包的三個屬性:SaveCheckpoints為True,CheckpointUsage為IfExists ,CheckpointFileName選定設定一個txt檔案即可（在目錄下建立一個.txt檔案）。 

2) 另外需要注意的是:如果我們希望一個任務可以設定檢查點的話，這個任務的FailPackageOnFailure屬性必須為True。我們設定分析資料更新價格的資料流的FailPackageOnFailure屬性為True.(這其實和我們上面的優先順序條件約束是有衝突的，因為現在話，即使這個任務執行失敗，也就直接導致整個包執行失敗了，也就不會執行下面的備份和刪除任務了。這裡我們只是為了分別示範不同的設定).

 

運行： 我們再次設定資料流中的資料轉換為組件失敗來導致整個資料流失敗，運行包，檢查一下剛才你所選擇的CheckpointFileName裡面有了很多資料。修改資料轉換任務為重新定行，運行包時，下載產品價格壓縮檔和解壓檔案兩個任務沒有運行，而是直接從分析資料更新價格開始的
 

在實際中，我們可以使用檢查點設定，在重新運行包時，不用再去重新運行沒有發生錯誤的，而且非常消耗資源和時間的任務。

 

--------------------------------------------------事務---------------------------------------------------------

六、 設定事務

如果分析資料更新價格這個資料流下面的檔案處理出錯時，會怎麼樣？我們的價格更新還會不會提交？

我們把刪除壓縮檔的路徑修改為user:filePath使其發生錯誤，經過對比發現，即使刪除壓縮檔這個任務發生了錯誤，上面的分析資料更新價格的任務也同樣提交了更改，產品的價格已經更新成功了。而這其實不是我們希望看到的，我們希望ProductPrice下面的檔案與我們資料庫的更新處理保持一致，在下面的檔案處理髮生錯誤時，上面的資料庫更新同樣也不提交。

這就需要事務了，需要在控制流程tab中設定Package包的事務屬性和任務級的屬性--TransactionOption對它的值作一個說明。

      Support        如果父物件中已經存在事務，則加入.
      Not Supported  即使存在一個事務，也不加入
      Required      事務是必須的，如果存在，加入存在的。如果不存在，啟動一個事務。
可以看到，我們現在的任務和包的TransactionOption的值都為Support，其實是沒有啟動事務的。那修改包的屬性為Required,又出錯了，不允許這樣的設定，提示:
不支援當前的包設定。請更改 SaveCheckpoints 屬性或 TransactionOption 屬性。

 

其實CheckPoings和Transaction也是相互有衝突的。一個事務的容器是一個可以再重新啟動切入的最小單位，整個事務要不都不執行，要不都執行，這才符合事務的特性。
為了減少兩者之間的衝突，SSIS在一個容器(包也是一個容器)沒有在一個任務中時，不去儲存它的檢查點資訊。 另外，Checkpoints在遇到Foreach容器中也會有同樣的問題，因為它不能夠儲存容器內部的資訊。(這裡有詳細的說明http://technet.microsoft.com/en-us/library/ms140226.aspx)
同樣，我們只是為了示範效果，先把SaveCheckpoints設定為False，把TransactionOption設定為True.再次運行包，對比前台的資料，發現當刪除壓縮檔失敗時，即時顯示更新價格任務執行成功，價格也沒有更新，此時程式包已經啟動了分散式交易。
這裡作為新手的我們都對檢查點與事務能不能同時在一個包中進行操作，產生了疑問，現在我也不打算馬上驗證，等有後續解決，或大家有解決方案，歡迎探討

 

---------------------------------------------------------設定事件處理常式-------------------------------------

七、 設定事件處理常式

是不是整個包不會出錯了？
當然不是，如果遠端不可訪問或者連線逾時，如果本地磁碟空間不足，如果更新資料庫時發生錯誤，都有可能導致整個包再次發生錯誤。而我們希望在發生這樣的錯誤時，能夠主動的通知我們。並告訴我們具體的錯誤資訊。
切換成事件處理常式，有兩個選擇下拉框，可執行檔和事件處理常式。在這裡我們只對Package的OnError進行響應，直接點擊下面的建立，在事件處理中我們可以使用與控制流程同樣多的任務類型.

 
    

  發送郵件我們使用和上次同樣的方法，使用指令碼發送郵件，只需要一個指令碼任務即可，具體的請看:http://www.cnblogs.com/lonely7345/archive/2009/09/03/1559579.html只需要擷取錯誤資訊作為郵件的內容

 

對指令碼不太熟悉，沒有沒成郵件的發送

 

-------------------------------------------設定日誌記錄------------------------------------------------------

八、 設定日誌記錄

我們最後來設定日誌記錄，點擊上面的SSIS菜單，選擇日誌記錄，這裡同樣可以選擇容器，還可以選擇日誌的提供類型，切換到詳細資料，我們可以選擇需要記錄的事件:

建立一個Log.txt文字檔到目錄下，配置時指向該文本：

 

我們選擇基於文字檔的記錄提供者，並建立存放日誌的檔案串連，選擇OnError和OnWarning為需要記錄的事件,執行包，查看Log.txt發現裡面有了詳細的錯誤資訊。我們同樣可以在這裡擴充提供者類型，使之能夠發郵件，達到和事件處理同樣的效果，而且更具有通用性，這裡也不深入下去了。

------------------------------------------------總結-----------------------------------------------------------

九、 總結
我們對資料的遠程下載、解壓、分析等操作進行了一些系列的示範，並且對事務、檢查點、事件處理常式、日記記錄、錯誤輸出都有了一定的實踐。因為本人也是初學著，對很原理的東西不是很清楚，但是通過對SSIS的學習，發現SSIS確實有資料的匯入匯出、資料分析等方面很強的功能！這樣如果運用好SQL和SSIS應該能有效關係資料，挖掘資料滿足業務需求。
P.S:Adventureworks資料庫，是個新手可以借鑒的資料，為什麼呢？通過對其梳理，發現裡面無論在命名、外鍵、主鍵、資料表歸類、資料結構上都有非常好的結構。

http://technet.microsoft.com/zh-cn/library/ms140185.aspx

 

 

 

項目step5原始碼檔案：版本為SQL 2008，運行代碼前還需要安裝WinRAR應用程式

/Files/cocole/Step5sql08.zip

 

作者：悟空的天空（天馬行空）
出處：http://www.cnblogs.com/cocole/
本文著作權歸作者和部落格園共有，歡迎轉載，但未經作者同意必須保留此段聲明，且在文章頁面明顯位置給出原文串連，否則保留追究法律責任的權利。