OLEDB數據源和目標組件

在SSIS工程的開發過程中，OLEDB 數據源和目標組件是最常用的數據流組件。從功能上講，OLEDB 數據源組件用於從OLEDB 提供者（Provider）中獲取數據，傳遞給下游組件，OLEDB提供者是外部數據源，SQL Server是其中一種OLEDB提供者；OLEDB目標組件用於把數據流插入到關係型數據表中。OLEDB數據源和目標組件非常靈活，支持動態傳參，功能強大，本文總結了這兩個組件經常被用到的屬性和用法。

一，數據源組件

查看數據源組件的編輯器，對數據源組件的配置，主要分為三大部分：連接管理器（Connection Manager），數據列（Columns）和錯誤輸出（Error Output）。

1，配置連接管理器

數據源組件的數據來源於外部數據源，SSIS從外部數據源中獲取數據，並把數據以數據流（Data Stream）的格式載入到SSIS引擎中。在連接到外部數據源之前，必須配置連接管理器，需要指定指定OLEDB 連接管理器，指定：Provider ，SQL Server實例名稱，身份驗證和連接的資料庫。在配置完成之後，可以點擊“Test Connection”測試連接管理器是否配置成功。

在列表“OLEDB connection manager”中選擇已經存在的連接管理器，或者點擊按鈕“New...”新建一個連接管理器，該連接管理器的作用域（Scope）是Package級別，只能用於當前Package；開發者可以通過在Project的頂層文件“Connection Managers”下，創建作用域為Project的連接管理器，當前Project的所有Package都能引用該連接管理器。

2，配置數據訪問模式

數據訪問模式是數據源組件獲取數據的方式，SSIS引起共提供四種數據訪問模式（Access Mode），分別是： 

• Table or View：從列表"Name of the table or the view"中選擇獲取數據的外部數據源（Table或View）。
• Table name or view name variable ：從列表“Variable name”中選擇變數，該變數的值是外部數據源（Table或View）的名稱，在選擇該模式時，必須把一個外部數據源（Table或View）的名稱存放在變數中；
• SQL command：在“SQL command text”輸入TSQL命令，可以在TSQL腳本中使用參數，在TSQL命令中以 ？ 代表參數，需要創建參數映射；
• SQL command from variable：將TSQL命令保存變數中，數據源組件從變數中獲取TSQL命令；

二，參數映射

最常用的數據訪問模式是SQL Command，這種模式有很大的靈活性和適用性，最根本的原因是該模式能夠在命令中使用參數，動態改變參數的值，能夠獲取到不同的數據源，實現海量數據的增量更新。

示例：在OLEDB數據源組件中，參數不是使用@VariableName表示，而是使用 ? 代表一個參數，在SQL 命令中，每一個 ? 都代表一個參數：

由於 ? 在SQL命令中出現的順序是固定的，因此可以通過序號0，1，2（序號從0開始）來定位到每一個參數，從而建立參數和變數之間的一一映射。

點擊按鈕“Parameters...”，打開“Set Query Parameters”窗體，把變數映射到SQL命令中的參數。

在中映射（Mappings）中， 參數 ? 的序號 和Parameters的名稱是相同的，變數（Variables）的名稱是在Package中創建變數（Variable），在Package執行時，SSIS引擎把變數的值傳遞給SQL命令。

變數在Variables窗體中創建，Name是變數的名稱，Scope是變數的作用域，分為Scope和組件兩個級別；DataType是變數的數據類型，這是CLR數據類型，在選擇上，需要考慮CLR數據類型和TSQL 數據類型的映射；Value是變數的值。

三，數據源組件的外部列

外部列，也是數據源的輸出列，點擊左側的“Columns”選項卡，能夠編輯數據源的外部列，從“Available External Columns”中能夠看到數據源所有可用的外部列，可以只輸出部分外部列，但是，該外部列已經從外部數據源中獲取，並傳輸到SSIS引擎中，對於無用的數據列，推薦從外部數據源中過濾，也就是從數據源組件的查詢命令中過濾，這樣，能夠減少SSIS引擎的緩存消耗和網路帶寬資源。

四，數據源組件的錯誤輸出

數據源組件的外部列是外部數據源的原始數據列，而數據源組件在獲取到外部數據之後，會向下游組件輸出，該數據列是輸出列，從外部列到輸出列，有一個數據列的轉換。而錯誤輸出，是指在數據源組件出現轉換錯誤時，配置數據列對錯誤的處理模式，數據列的轉化，是指從外部列轉化到數據源定義的輸出列上，常用的轉換錯誤是錯誤（Error）和截斷（Truncation）。

從描述（Description）中可以看出，數據列的錯誤是指數據轉化（Conversion）錯誤。

錯誤處理模式共有三種，分別是：

• Ignore failure：忽略錯誤，是指數據源出現錯誤時，直接忽略錯誤，而使數據源組件正常運行下去；
• Redirect row：重定向錯誤行，是指把錯誤的數據行重定向到另外一個數據目標組件中；
• Fail component：組件錯誤，是指當錯誤時，數據源組件拋出異常，數據源組件停止運行，對已經導入到數據目標組件中的數據，不做改變。

五，數據源組件的高級編輯器 

 點擊Edit，或直接雙擊數據源組件，彈出的是數據源編輯器，通過“Show Advanced Editor”按鈕，彈出的是高級編輯器，能夠編輯數據源組件的底層屬性。

高級數據源組件，能夠編輯數據源的：連接管理器，組件屬性，列映射和 輸入/輸出屬性。

在Connection Managers中，顯示數據源組件使用的連接管理器。

1，組件屬性

在Component Properties中，能夠修改數據源組件的底層屬性，在通用屬性（Common Properties）列表中，最重要的屬性是：ValidateExternalMetadata，用於指定該組件是否在設計時（design-time）驗證外部數據源的元數據（metadata），如果設置為false，SSIS引擎延遲到Package運行時（runtime）驗證外部數據源的元數據。

用戶屬性（Custom Properties）和前文的提到的屬性相同，不再贅述。

2，列映射

列映射是外部列和輸出列的映射，和上文的第三節（三，數據源組件的外部列）相同，不再贅述。

3，輸入和輸出屬性

點擊分類“OLEDB Source Output”，從右邊的“Common Properties”列表中查看數據源組件的輸出屬性，大多數屬性是無法編輯的：

最重要的一個屬性是：IsSorted，指定數據源是否已經排序，如果外部數據源通過 order by 子句排序，可以設置IsSorted屬性為True。

通常情況下，關係型資料庫的排序，比SSIS引擎的排序組件，執行性能更高，推薦在外部關係型資料庫中執行排序，並輸出已排序的數據。

外部列（External Columns）是外部數據源的數據列，點擊外部列，能夠編輯外部列的元數據，示例，點擊外部列id，能夠查看外部列的屬性，並編輯外部列的數據類型。

輸出列（Output Columns）是數據源組件向下游組件輸出的數據列，在輸出列和外部列之間存在元數據的轉換，上文提到，轉換分為兩種類型：Error和Truncation，在輸出列的通用屬性（Common Properties）中，

轉換屬性：ErrorRowDisposition和TruncationRowDisposition用於指定在轉換出現錯誤時的處理模式，這在上文第四節（四，數據源組件的錯誤輸出）中已提到，不再贅述。

通用屬性中，最重要的屬性是：SortKeyPosition，預設值是0，表示該數據列不是排序列。 正整數表示升序，負整數表示降序，排序列的序號，從1依次遞增。

如果外部數據源的IsSorted屬性設置為true，那麼請設置輸出的排序列的排序鍵的位置，

例如，外部列按照 order by id asc,name desc排序，那麼 id的 SortKeyPosition屬性值是1，name的SortKeyPosition屬性值是-2。

六，OLEDB 目標組件

目標組件的作用是把數據流載入到關係表中，目標組件在內部使用 insert 或bulk insert 命令把上游組件傳遞的數據插入到目標關係表中。目標組件共有5種數據訪問模式，常用的是：“Table or view” 和 “Table or view - fast load”。

這兩種數據訪問模式的異同之處是：

• “Table or view” 模式：目標組件在內部使用insert命令，把數據插入到目標關係表中。
• “Table or view - fast load”模式：組件在內部使用bulk insert命令，把數據插入到目標關係表中；使用Fast load 選項，能夠顯著提高數據插入的性能。

1，設置fast load選項的屬性

當選擇fast load選擇的數據訪問模式時，目標組件內部使用bulk insert命令批量向目標關係表插入數據，目標組件的UI界面上額外增加以下屬性，

這些屬性代表的含義分別是：

• Keep identity ：保持ID值，當目標表中存在標識列時，如果不勾選“Keep identity”，那麼目標表的ID列插入失敗，如果勾選“Keep identity”，那麼目標表中的ID列和數據源保持相同。
• Keep Nulls：如果目標表為某一列設置了default約束，當數據流傳遞null給該列時，正常情況下會觸發目標表的default約束。如果不選中Keep Nulls，那麼目標表使用default value來填充數據列；如果選中Keep Nulls，那麼目標表保持該列為null。
• Table Lock：如果勾選該選項，在數據載入期間，整個目標關係表加上表級鎖。
• Check Constraints：檢查插入的數據是否符合目標關係表約束，如果事先能夠確定插入的數據流符合目標關係表的約束，那麼不勾選“Check Constraints”會顯著提高數據插入的性能。

當插入大量數據的時候，適當控制 tempdb 和單個事務的大小，能夠提高數據插入的的性能。

• Rows per batch：在執行bulk insert時，配置每一個batch 插入的數據行的數量，預設值是-1，表示不指定數值，由SSIS引擎自主確定每個batch的數據行數量；
• Maximum insert commit size：指定數據源組件提交一個事務時已經處理的數據行的數量，也就是說，當插入指定數量的數據行時，數據源組件提交一個事務，通過配置該選項，能夠控制單個事務的大小；
  • 如果設置屬性值為0，指定在一個事務中完成插入所有的數據行，在一個事務中插入所有的數據行，可能導致事務日誌過大；另外，如果數據源在導入期間被修改，目標組件會停止響應。
  • 在插入一批數據行時，batch中的任意一條數據違反約束，資料庫引擎將回滾整個事務，這意味著，整個batch的數據行插入操作是失敗的。

2，目標組件的高級屬性

目標組件也有高級編輯器，其輸入和輸出屬性，分為外部列和輸入列，外部列是目標組件接收的數據列，輸入列是目標組件把數據插入到指定的關係表的數據列。如下圖所示：

 當目標關係表的元數據和目標組件的輸入列的元數據不一致時，需要手動同步，才能插入成功。