SQL Server BI Step by Step SSIS 4 —合并資料2

    上次我們並沒有實現Excel中的資料與資料庫中的資料進行整合,存在即更新,不存在即插入.這次主要介紹幾種方法來實現:
     1.使用Lookup
     2.使用execute SQL task調用預存程序
     3.使用script component指令碼實現
     4.使用MERGE 語句(SQL SERVER 2008)
     5.使用上次我們用到的Merge來實現
     6.使用第三方組件SCD Component

    看起來能夠實現的方法確實不少,我們來一一介紹,介紹的同時也會介紹一些組件的應用,同理在我們實現其它功能時,也可以同樣使用.

1. Lookup

     建立一個包MergeDataLookUp,我們把ForeachInput中的複製過來,我們實現遍曆Excel的同時,實現Excel中的資料與資料庫中的資料合併,在資料流中,在Excel資料來源的下面,刪除原來的組件,拖入LookUp組件,選擇OLE DB串連後,進行尋找的設定: 
      

      可以看到,我們是根據Excel資料中的ProductNumber欄位去資料庫中尋找對應的資料(列ProductID),即找到對應的資料時,ProductID會作為新列添加到我們的資料流中,找不到時則會出現錯誤,點擊上面的標出的配置錯誤輸出,

     

     這樣對於Lookup的兩個輸出,正常的輸出也就是找到了ProductNumber對應的資料,此時做更新操作.中我們已經進行了配置,當某一行沒有找到時,我們將這行資料重新定行到錯誤輸出,此時再進行插入操作.我們在正常的輸出上添加OLE DB Command,來執行我們的Update語句.
    
    

   在錯誤輸出上添加我們上次添加過的OLE DB Destination,其中忽略ProductID和rowguid兩個欄位.整個資料流如下:
  

   到現在我們已經實現了利用Lookup合并資料.

2.使用預存程序
      使用預存程序實現這個就相當容易的多,不過並不建議這樣做,我們把所有的流程都放在了預存程序裡面,而不是SSIS包裡面,有一個應該考慮的是,一般我們可以在SSIS包裡設定成支援事務(設定包或者組件的TransactionOption屬性).在預存程序裡,我們可以直接採用預存程序裡面的事務機制.
      我們建立一個包MergeDataProcedure,完成如上個包的設定, 只需要執行一個OLE DB Command,在這裡我們調用預存程序,預存程序裡我們只是完成根據ProductNumber判斷資料存在不存在,存在即執行更新,不存在插入.在這裡就不再對這個方法進行詳細介紹了.

3.使用Script Component

     建立一個包MergeDataScript,複製MergeDataLookUp中的控制流程和變數,連接器,將Loopup組件換成Script Component組件(添加時使用方法為轉換).首先選擇輸入列,並設定其使用類型:
    

    然後設定輸入輸出,在這裡,有一個輸入,然後設定三個輸出,輸出的列不需要手動設定的,是自動建立的,注意到輸入RecordsInput的ID為2778.三個輸出的名稱分別為UpdateRecordsOutput,InsertRecordsOutput,IgnoreRecordsOutput,將輸出的屬性ExclusionGroup設定成1,將屬性SyncronousInputID值設定成RecordsInput(ID為2778),每個輸入控制項的SyncronousInputID都是不一樣的.設定這兩個屬性是我們下面指令碼啟動並執行關鍵,具體將查詢官方文檔.
   

    最後我們設定一下連線管理員,因為我們在指令碼中要擷取資料庫連接,所以在這裡我們添加一個串連的名稱,注意這裡我並沒有串連到原來的OLE DB串連,我在指令碼中使用了SqlDataReader,此處需要建立一個ADO.NET串連.
     
    設定完以後,我們再切換到指令碼,直接設定指令碼,開啟指令碼編輯器,輸入如下指令碼,然後關閉.確定.
   

Imports SystemImports System.DataImports System.MathImports Microsoft.SqlServer.Dts.Pipeline.WrapperImports Microsoft.SqlServer.Dts.Runtime.WrapperImports Microsoft.SqlServer.Dts.RuntimeImports System.Data.SqlClientPublic Class ScriptMain    Inherits UserComponent    Dim connMgr As IDTSConnectionManager90    Dim sqlConn As SqlConnection    Dim sqlCmd As SqlCommand    Dim sqlParam As SqlParameter    Public Overrides Sub AcquireConnections(ByVal Transaction As Object)        connMgr = Me.Connections.DBConnection        sqlConn = CType(connMgr.AcquireConnection(Nothing), SqlConnection)    End Sub    Public Overrides Sub PreExecute()sqlCmd = New SqlCommand("SELECT [Name] FROM Product WHERE ProductNumber=@ProductNumber", sqlConn)        sqlParam = New SqlParameter("@ProductNumber", SqlDbType.NVarChar, 25)        sqlCmd.Parameters.Add(sqlParam)    End Sub    Public Overrides Sub RecordsInput_ProcessInputRow(ByVal Row As RecordsInputBuffer)        Dim reader As SqlDataReader        sqlCmd.Parameters("@ProductNumber").Value = Row.ProductNumber        reader = sqlCmd.ExecuteReader()        If reader.Read() Then            '此處可以根據需要進列欄位的比較
If (reader("Name").ToString() <> Row.Name) Then                Row.DirectRowToUpdateRecordsOutput()            Else                Row.DirectRowToIgnoreRecordsOutput()            End If        Else            Row.DirectRowToInsertRecordsOutput()        End If        reader.Close()    End Sub  Public Overrides Sub ReleaseConnections()        connMgr.ReleaseConnection(sqlConn)    End SubEnd Class

   上面的指令碼的具體含義也就不再詳細介紹,比較容易理解,其實與我們使用LookUp實現的功能相同,使用ProductNumber進行尋找Name欄位,如果找到Name則跳轉到更新輸出,否則跳轉到忽略輸出,如果沒有找到,則跳轉到添加輸出.我們也可以直接把添加,更新這些操作放在指令碼裡面.不過為了使整個流程更加清晰,我們只是使用指令碼進行了一個轉換.不過其實指令碼實現的會更加靈活,這裡其實還可以實現雙向尋找或者是更加複雜的功能.

   我們在Script Component後面添加對應的輸出,其中UpdateRecordsOutput,InsertRecordsOutput和使用LookUp一樣.不過我們對於IgnoreRecordsOutput輸出我們添加一個RowCount進行統計.

  

執行包,完成了資料的更新和添加.

 

好吧,有些困了,今天就只介紹這三種方法的實現,下次再簡單的介紹另外三種方法.

參考文章:

http://www.mathgv.com/sql2005docs/SSISTransformScriptETL.htm

http://blogs.conchango.com/jamiethomson/archive/2006/09/12/SSIS_3A00_-Checking-if-a-row-exists-and-if-it-does_2C00_-has-it-changed.aspx

http://www.sqlis.com/post/Get-all-from-Table-A-that-isnt-in-Table-B.aspx

     本次專案檔下載.(for Vs 2005)

 

作者：孤獨俠客（似水流年）

出處：http://lonely7345.cnblogs.com

本文著作權歸作者和部落格園共有，歡迎轉載，但未經作者同意必須保留此段聲明，且在文章頁面明顯位置給出原文串連，否則保留追究法律責任的權利。