優化 SSIS 包以減少查詢

當客戶下訂單時，我需要將客戶的訂單與他們的“級別”（銀、金等）相關聯：

CRM server::CRM db::CRM table
----------
CustomerID   PreviousLevel    NewLevel    NewLevelGrantedOn

Order server::Order db::Order table
----------
OrderID     CustomerID    OrderPlacedOn

在 SSIS 包中，我這樣做了：

1. 對 Order db 執行 SQL 以提取訂單並將其放入對像變數中；
2. 使用“foreach 容器”循環遍歷每個訂單，我在其中放置了一個select top 1 * where CustomerID = ? and LevelGrantedOn < ? order by LevelGrantedOn desc從 CRM 數據庫中提取數據的數據流任務（兩個參數都來自第一步），派生一些列並將輸出寫入另一個表。

order db中有兩萬多條記錄，這意味著數據流任務將執行兩萬次以上。CRM 數據庫也將被查詢超過兩萬次。完成這些需要一個多小時。

我可以利用一些內置功能來加速這些（或以“智能”方式進行）嗎？而且，在 ETL 和/或 SSIS 的背景下，一個小時是否很長？

您可以在不使用 Foreach 列舉器的情況下實現這一點，只需使用一個數據流任務來實現這一點。

建構包

首先，在控制流中添加一個數據流任務

1. OLEDB 來源

在 DataFlow 任務中添加一個OLEDB Source從訂單表中讀取的命令（與Execute SQL Task （您問題的第一步）中使用的命令相同）

Select * FROM Order

還要添加第二個OLEDB Source從客戶表中讀取的內容：

select * FROM CRM

2.排序

在每個 OLEDB Source 之後添加一個源組件：

1. 您必須選擇的第一個（訂單表）CustomerID和OrderID列進行排序，並將排序類型選擇為升序
2. 您必須選擇的第二個（客戶表）CustomerID *（排序類型 = 升序）*和LevelGrantedOn *（排序類型 = 降序）*列進行排序

3.合併加入

添加一個合併連接組件以連接兩個排序的輸出。並CustomerID從兩個輸出中選擇列作為連接鍵並從兩個表中選擇所需的輸出列

4. 條件拆分

合併加入後添加條件拆分以僅過濾與以下表達式匹配的行

[LevelGrantedOn] < [orderdate]

5.腳本組件

我們必須使用的最後一個組件是一個腳本組件，它只獲取每個 customerID 的第一行*（因為兩個源都很好地排序，只是抓取第一行是相似的Select top 1 ... ORDER BY LevelGrantedOn desc）*

在腳本組件OutFlag中添加類型的輸出列DT_BOOL並使用以下腳本：

此腳本將在 customerID 第一次出現時將 OutFlag 設置為 True（類似於TOP 1）

   Public Class ScriptMain
       Inherits UserComponent


       Dim lstCustomerID As New System.Collections.Generic.List(Of Integer)
       Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)

           If lstCustomerID.Contains(Row.ID) Then

               Row.OutFlag = False
           Else

               lstCustomerID.Add(Row.ID)
               Row.OutFlag = True


           End If



       End Sub

   End Class

6. 條件拆分

添加第二個條件拆分以過濾前 1 行：

[OutFlag] == True

7.OLEDB目的地

將條件拆分輸出連接到 OLEDB 目標頂部並映射列。

數據流任務必須看起來像

引用自：https://dba.stackexchange.com/questions/176886