SQL Server BI Step by step 4-2 合并資料 LookUp組件和Script Component組件完成資料合併

 合并資料2
----LookUp組件和Script Component組件完成資料合併

 

本章實現Excel中的資料與資料庫中的資料進行整合時,存在即更新,不存在即插入.這次主要介紹幾種方法來實現:
     1.使用Lookup
     2.使用execute SQL task調用預存程序
     3.使用script component指令碼實現
     4.使用MERGE 語句(SQL SERVER 2008)
     5.使用上次我們用到的Merge來實現
     6.使用第三方組件SCD Component
看起來能夠實現的方法確實不少,我們來一一介紹,介紹的同時也會介紹一些組件的應用,同理在我們實現其它功能時,也可以同樣使用.

一、 LookUp組件
 目的：實現遍曆多個Excel源的同時，實現Excel中資料和資料庫中資料合併，即：存在資料更新，不存在資料插入。

  

 

 

 

 

 

 

 

 

第二次迴圈，可以測試更新資料哦！同理。

 
 
2. 建立一個包MergeDataLookUp。
3. 建立一個Foreach迴圈容器，在裡放入資料流程工作，進行編輯。按照《Step3  大量匯入Excel》配置該迴圈。注意組態變數和修改Foreach的屬性哦！！！
 
4. 切換到資料流tab頁，拖一個excel源串連Product1.xls、LookUp組件、OLE DB Command組件、衍生的資料行組件、OLE DB目標元件連線Product表用於儲存“錯誤”紀錄，並插入該紀錄。

 
5. 對LookUp組件設定。
對tab參考資料表設定：
 
tab列設定：
通過 Excel資料中的ProductNumber欄位去資料庫中尋找對應的資料（尋找的紀錄行數等於Excel資料來源的行數）；
選擇ProductID為LookUp查詢後資料流顯示的列。
當找不到對應資料時，點擊上面的標出的配置錯誤輸出。

 
對“配置錯誤輸出”設定
 
若不設定，則會報錯：
-----------------------------------------------報錯---------------------------------------------------------------------------------------
錯誤: 0xC020901E，位於 資料流程工作, 尋找 [39]: 在尋找期間行沒有產生任何匹配項。
錯誤: 0xC0209029，位於 資料流程工作, 尋找 [39]: “組件“尋找”(39)”失敗，錯誤碼為 0xC020901E，而且針對“輸出“尋找輸出”(41)”的錯誤行處理設定指定一旦出錯就失敗。在指定組件的指定對象上出錯。
--------------------------------------------------------------------------------------------------------------------------------------------

6. 對OLE DB Command組件添加Update命令來控制輸出。
 

 

 

UPDATE  Production.Product  SET   [Name]=?,MakeFlag=? WHERE ProductID=?

 

設定UPDATE欄欄位與參數的映射。一定要將LookupID對應參數！參數怎麼來的呢？呵呵，就是你剛剛Update語句中的參數。
 

7. 設定衍生的資料行組件
 

8. 設定OLE DB  ,把RroductID、rowguid設定為忽略。Name,ProductNumber映射為衍生類別的新列。
 
完成，這8個步驟後，加入你關心的資料流查看器，就可以以執行包：
第一次，執行成功！
 
 

第二次，運行。

 

 

資料庫中，資料庫在第一次運行時，插入兩行紀錄

。
 

細心的朋友，可能會看出兩次Lookup後尋找到的更新輸出、錯誤輸出裡的值都沒有變化，而第一次執行後資料庫的值就已經插入。而正常情況下，更新輸出應該是4行了，而錯誤輸出是0行！而整個運行沒有錯誤！
WHY？猜想這個時候用了緩衝或是SQL尋找後的視圖又或者？？，而導致沒有真正去LOOKUP新資料庫而直接讀出資料。那這怎麼辦呢？  問題哦！

 

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

 

三、 使用Script Component
1. 建立一個包MergeDataScript。
2. 添加控制流程和Excel源組件。
3. 添加Script Component組件。
選擇指令碼控制項要使用——轉換
 
 

然後設定輸 然後設定輸入輸出。有一個輸入,三個輸出命名為UpdateRecordsOutput,InsertRecordsOutput,IgnoreRecordsOutput,手動設定。注意到輸入RecordsInput的ID為115.將輸出的屬性ExclusionGroup設定成1,將屬性SyncronousInputID值設定成RecordsInput的ID為115。（注意：每個輸入控制項的SyncronousInputID都是不一樣的.設定這兩個屬性是我們下面指令碼啟動並執行關鍵,具體將查詢官方文檔）

 

編輯連線管理員。
因為從指令碼中要擷取資料庫連接,這裡需要的是SqlDataReader,建立一個ADO.NET串連。
建立ADO.NET串連

  
 

指令碼連線管理員串連到建立的ADO.NET
 

 

編輯指令碼

指令碼

Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper
Imports Microsoft.SqlServer.Dts.Runtime.Wrapper

Imports Microsoft.SqlServer.Dts.Runtime
Imports System.Data.SqlClient

Public Class ScriptMain
    Inherits UserComponent

    Dim connMgr As IDTSConnectionManager90

    Dim sqlConn As SqlConnection

    Dim sqlCmd As SqlCommand

    Dim sqlParam As SqlParameter

    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 AcquireConnections(ByVal Transaction As Object)

        connMgr = Me.Connections.DBConnection

        sqlConn = CType(connMgr.AcquireConnection(Nothing), SqlConnection)

    End Sub

    Public Overrides Sub 輸入0_ProcessInputRow(ByVal Row As 輸入0Buffer)
        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 Sub

End Class

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

 

4. 在ScriptComponent設定了三個輸出，UpdateRecordsOutput對應OLE DBCommand組件,InsertRecordsOutput對應OLE DB目標組件,IgnoreRecordsOutput對應行計數組件

 

5. 設定OLE DBCommand組件。

• 連線管理員設定為原來OLE DB管理器
• 設定Command
  

  UPDATE Production.Product  SET   [Name]=?  WHERE ProductNumber=?

•  列映射

6. 設定OLE DB目標組件，對應原來OLE DB管理器，指向Product表。
（UpdateRecordsOutput,InsertRecordsOutput和使用LookUp一樣）

7. 設定行計數組件。

• 添加變數，範圍就是該資料流程工作

在行計數組件的屬性中，找到VariableName設定為剛剛變數——使用者::RowIgnore。
 

執行：我的問題就是，輸出顯示成功，但是資料流沒有資料。

 

 

項目step1---4原始碼檔案：版本為SQL 2005，運行代碼前還需要安裝ExceL應用程式

/Files/cocole/Step1-4Sql05.rar

 

 

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