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

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

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

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

變更名	類型	值	說明
filePath	String	D:\ProductPrice\	資料根目錄
dataPath	String	D:\ProductPrice\ProductList.txt	解壓後資料檔案
backPath	String	@[User::filePath]  +   "bak\\"  + (DT_WSTR, 4) YEAR( GETDATE() ) + "-" + (DT_WSTR, 2) MONTH( GETDATE() ) + "-" +  (DT_WSTR, 2) DAY( GETDATE() )   + ".zip"	需要將EvaluateAsExpression設定為True 主要用於儲存未執行成功的壓縮資料檔案，其名稱為當前日期(由運算式計算出當前日期) 比如:D:\ProductPrice\bak\2009-9-20.zip
WinRar	String	C:\Program Files\WinRAR\WinRAR.exe	WINRAR的安裝目錄
zipPath	String	D:\ProductPrice\Price.zip	zip壓縮檔下載路徑
erroCount	Int32	0	資料檔案解析出錯的行數

      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檔案下載到本地的檔案夾中. 
      6.現在我們來完成解壓的任務，首先需要確認本地已經安裝了WinRar應用程式。添加執行進程任務,設定屬性WindowStyle屬性為Hidden,進行運算式設定，分別對屬性進行如下設定:

屬性	運算式	說明
Executable	@[User::WinRar]	可執行程式Winrar路徑
WorkingDirectory	@[User::filePath]	執行目錄
Arguments	"e "  +   @[User::zipPath]  + "   *.txt  -y "	WinRar命令列參數，解壓@zippath檔案中所有的.txt檔案，y代表如果存在直接覆蓋

         執行程式包，d盤ProductPrice目錄下會同時出現兩個檔案，一個zip壓縮檔，和一個ProductList.txt檔案。
      7.建立一個檔案串連，指向我們的D:\ProductPrice\ProductList.txt檔案，選中Unicode,對列進行設定。但是，我們的檔案路徑是變數，不能夠寫死在這裡，所以在屬性裡對其Expressions設定，設定ConnectionString的值為@[User::dataPath].
      8.添加資料流程工作.在資料流程工作中首先添加一個一般檔案源，串連我們的剛才建立的檔案串連。檔案中只有兩個列，一個是產品編號，一個是產品價格。兩個都為DT_WSTR類型。

PrdocutNumber    ListPrice
AR-5381    22.0
BA-8327    
BE-2349    12
BE-2349   
BE-2908    122.2
添加資料轉換任務，我們將產品價格轉換為數字類型:

執行程式包，出錯了，錯誤就發產生我們的資料轉換任務，對於空的字串，不能夠將其轉換成數字類型，怎麼辦？點擊左下角的配置錯誤輸出，這裡我們可以指定行級錯誤，對出錯的行選擇:
      1.組件失敗: 導致整個任務執行失敗.
      2.忽略失敗: 忽略這個行的出錯，對於這個任務，忽略的行的PriceList的值為NULL
      3.重新定行: 失敗的資料，可以重新定義輸出.
在這裡，我們選擇重新定行:
   
    同理，我們也可以對上面的檔案串連進行同樣的錯誤輸出處理，以防止在讀取檔案時，由於資料原因發生錯誤。

       9.添加一個OLE DB命令，完成資料的更新。SqlCommand為:

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

  在列映射欄裡，對兩個參數進行映射，注意前後的順序。
      10.添加行計數任務，注意，從資料轉換任務的下面拖拉紅色的錯誤輸出到行計數下面，使我們出錯的行資料流入這個任務。設定行計數任務的變數為@errorcount.再次執行包，錯誤沒有了，探索資料轉換下面已經分成3行的正確走向，和2行的錯誤走向。

 

 

        11.添加對檔案的處理。添加三個檔案系統執行到控制流程中，分析執行刪除壓縮檔，備份壓縮檔，刪除資料檔案。注意，均使用變數的方式（具體見下載源檔案).我們希望在資料流執行失敗時執行備份壓縮檔，在這裡我們需要設定優先順序條件約束,預設的是綠色的完成時執行。右擊串連，選擇失敗,
    
  不管執行成功還是失敗，我們都希望在最扣刪除資料檔案，我們又將兩個檔案系統任務同時指向了刪除資料檔案任務。而我們需要設定他們之間任務一個執行完成時即執行，象上面一樣，右擊選擇編輯進入優先順序條件約束編輯器，我們選擇邏輯或，即兩個任務有一個執行成功即可:

      12.我們來看一下上面設定的優先順序條件約束的效果。修改資料流中的資料轉換任務先後為組件錯誤和重新定行：
           

       13.雖著產品數量的增多，下載的壓縮包太大，每次執行失敗時，我們希望能夠從執行失敗的任務直接運行，而不用都要重新去下載和解壓.我們來設定Checkpoings，檢查點來完成這樣的功能。首先設設定包的三個屬性:SaveCheckpoings為True,CheckpoingUsage為IfExists ,CheckpointFileName選定設定一個txt檔案即可。  另外需要注意的是:如果我們希望一個任務可以設定檢查點的話，這個任務的FailPackageOnFailure屬性必須為True。我們設定分析資料更新價格的資料流的FailPackageOnFailure屬性為True,(這其實和我們上面的優先順序條件約束是有衝突的，因為現在話，即使這個任務執行失敗，也就直接導致整個包執行失敗了，也就不會執行下面的備份和刪除任務了。這裡我們只是為了分別示範不同的設定). 我們再次設定資料流中的資料轉換為組件失敗來導致整個資料流失敗，運行包，檢查一下剛才你所選擇的CheckpointFileName裡面有了很多資料。修改資料轉換任務為重新定行，運行包時，下載產品價格壓縮檔和解壓檔案兩個任務沒有運行，而是直接從分析資料更新價格開始的:
            

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

         14.如果分析資料更新價格這個資料流下面的檔案處理出錯時，會怎麼樣？我們的價格更新還不會不會提交？我們把刪除壓縮檔的路徑修改為user:filepath使其發生錯誤，經過對比發現，即使刪除壓縮檔這個任務發生了錯誤，上面的分析資料更新價格的任務也同樣提交了更改，產品的價格已經更新成功了。而這其實不是我們希望看到的，我們希望ProductPrice下面的檔案與我們資料庫的更新處理保持一致，在下面的檔案處理髮生錯誤時，上面的資料庫更新同樣也不提交。這就需要事務了，需要設定程式包的事務屬性和任務級的屬性--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.再次運行包，對比前台的資料，發現當刪除壓縮檔失敗時，即時顯示更新價格任務執行成功，價格也沒有更新，此時程式包已經啟動了分散式交易。
         15.是不是整個包不會出錯了？當然不是，如果遠端不可訪問或者連線逾時，如果本地磁碟空間不足，如果更新資料庫時發生錯誤，都有可能導致整個包再次發生錯誤。而我們希望在發生這樣的錯誤時，能夠主動的通知我們。並告訴我們具體的錯誤資訊。切換成事件處理常式，有兩個選擇下拉框，可執行檔和事件處理常式。在這裡我們只對Package的OnError進行響應，直接點擊下面的建立，在事件處理中我們可以使用與控制流程同樣多的任務類型.
      發送郵件我們使用和上次同樣的方法，使用指令碼發送郵件，只需要一個指令碼任務即可，具體的請看:http://www.cnblogs.com/lonely7345/archive/2009/09/03/1559579.html只需要擷取錯誤資訊作為郵件的內容.

string  body  =  "Package failed error: " + Dts.Variables["ErrorDescription"].Value.ToString();

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

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

 

結束:
     採用SSIS完成一個實際的功能來介紹了SSIS包的事務，檢查點，事件處理,錯誤輸出，優先順序條件約束，日誌記錄等方面，同時也介紹了SSIS的運算式，執行進程任務，下載遠程檔案等很多細節.SSIS確實強大，在這麼短的幾次文章當中很難涵蓋它的方方面面，雖然結束的有些匆忙，但是很高興這一系列的SSIS到這次就結束了，希望這些簡單的例子能夠對入門ssis的朋友有些協助，也希望我們能夠分享它的強大。下次將開始SQL Server BI Step by Step SSRS  --- reporting service 2008.

下載:
    http://files.cnblogs.com/lonely7345/DownloadRssNews.rar

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