Data Flow

什麼是 Data Flow Task

When creating an ETL project, you will spend most of the time building various kinds of data flow tasks. Each package can have zero or more data flow tasks. There are three types of data flow task components in the SSIS Toolbox：

• Data flow source adapters
• Data flow transformations
• Data flow destination adapters

定義 Data Sources 和 Data Destinations

Data Flow Adapters 可分成來源和目的地二大類，你必須針對不同的資料來源類型，使用不同的 Data Flow Adapters 。

Data Source Adapters

ADO NET Source CDC Source Excel Source ODBC Source OLE DB Source XML Source Flat File Source Raw File Source OData Source

Data Sources 資料存取模式

OLE DB 來源提供四種不同資料存取模式用來擷取資料(extracting data)：

• A table or view
• A table or view specified in a variable.
• The results of an SQL statement
• The results of an SQL statement stored in a variable.

來源小幫手（Source Assistant）

來源小幫手元件可以用來協助建立來源元件和連線管理員。

Data Destination Adapters

ADO NET Destination DataReader Destination Excel Destination ODBC Destination OLE DB Destination SQL Server Compact Edition Destination SQL Server Destination Flat File Destination Raw File Destination Dimension Processing Destination Partition Processing Destination Data Mining Model Training Destination Recordset Destination

Data Destination 資料存取模式

OLE DB 目的地提供五種不同資料存取模式用於載入資料(loading data)：

• A table or view
• A table or view using fast-load
• A table or view specified in a variable.
• A table or view specified in a variable using fast-load options
• The results of an SQL statement

快速載入選項

如果 OLE DB 目的地使用快速載入（Fast Load）資料存取模式，則您可以為目的地指定下列快速載入選項：

• 保留識別 ：保留匯入資料檔中的識別值或者使用 SQL Server 指派的唯一值。
• 保留Null ：大量載入作業期間，保留 Null 值。
• 資料表鎖定 ：大量載入作業期間，需要使用資料表層級鎖定。
• 檢查條件約束：大量匯入作業期間，檢查目的端的 table 或 view 的條件約束。
• 每批次的資料列：指定批次處理的資料列數目（batch size）。
• 插入認可的上限：指定認可的資料列數目（commit size）。

SSIS Data Types

當建立資料來源時，由於資料來源可能是 SQL、Oracle 或者 Flat File ， 所以 SSIS 會自動依據資料來源中的內容，自動決定一個SSIS的資料型別。 你可以針對實際上的需求自行調整資料的型別設定，以免資料在執行 Transformation 時發生型別錯誤。

你可以在［進行編輯］功能中，找到SSIS為每個欄位資料設定的輸出型別：

使用「快速剖析（Fast Parse）」

Fast Parse is a set of operations within SSIS that can be used for very fast loading of flat file data. Fast Parse will not parse any locally sensitive data so it can load data very quickly.

Fast Parse has limited functionality, because it works only for specific data types. But if you have a lot of columns of date, time, or integer data types or very large files, Fast Parse is the fastest method for importing data files.

Data Flow 元件常用的屬性

下列列表是幾個 Data Flow 元件常用的屬性：

• CommandTimeout：設定元件Timeout的時間。
• ValidateExternalMetadata：是否在 design time 時執行外部資料的驗證。

Working with Transformations

轉換元件（Transformations）

轉換（Transformations）是SSIS資料流程中的元件。 專門用來處理彙總、合併、散發和修改資料等作業；也可用來執行查閱作業，以並產生資料集範本。 下面依造功能分類，列出 SSIS 中的轉換元件。

商業智慧轉換（Business Intelligence Transformations）

下列轉換會用來執行與商業智慧相關的作業 (例如，清除資料、採礦文字及執行資料採礦預測查詢)。

Transformations	轉換	說明	Block
Slowly Changing Dimension Transformation	緩時變維度轉換	configures the updating of a slowly changing dimension.
Fuzzy Grouping Transformation	模糊群組轉換	standardizes values in column data.
Fuzzy Lookup Transformation	模糊查閱轉換	使用模糊比對
Term Extraction Transformation	詞彙擷取轉換	extracts terms from text.
Term Lookup Transformation	詞彙查閱轉換	looks up terms in a reference table.
Data Mining Query Transformation	資料採礦查詢轉換	執行資料採礦預測查詢
DQS Cleansing Transformation	DQS 清理轉換	在來源資料中建立規則以更正來源資料

資料列轉換（Row Transformations）

這類轉換元件會針對輸入中的每一個資料列，進行資料行的更新，然後再以建立新的資料行輸出。

Transformations	轉換	說明	Block
Character Map Transformation	字元對應轉換	applies string functions to character data.
Copy Column Transformation	複製資料行轉換	adds copies of input columns to the transformation output.
Data Conversion Transformation	資料轉換	資料型別的轉換
Derived Column Transformation	衍生的資料行轉換	「衍生的資料行」轉換會將運算式套用至轉換輸入資料行，藉此建立新的資料行值。
Import Column Transformation	匯入資料行轉換	inserts data from a data flow into a file.
Export Column Transformation	匯出資料行轉換	that reads data from a file and adds it to a data flow.
Script Component	指令碼元件	uses script to extract, transform, or load data.
OLE DB Command Transformation	OLE DB 命令轉換	runs SQL commands for each row

Derived Column Transformation

資料列集轉換（Rowset Transformations）

下列轉換元件會產生新的資料列集輸出。

Transformations	轉換	說明	Block
Aggregate Transformation	彙總轉換	執行彙總 (例如 AVERAGE、SUM 及 COUNT) 的轉換。
Sort Transformation	排序轉換	排序資料的轉換。
Percentage Sampling Transformation	百分比取樣轉換	透過使用百分比指定範例大小來建立樣本資料集的轉換。
Row Sampling Transformation	資料列取樣轉換	透過指定樣本中的資料列數目來建立樣本資料集的轉換。
Pivot Transformation	樞紐轉換	creates a less normalized version of a normalized table.
Unpivot Transformation	取消樞紐轉換	creates a more normalized version of a nonnormalized table.

分割與合併轉換（Split and Join Transformations）

下列轉換元件會將一個輸入散發成多個輸出，或者合併多個輸入到一個輸出。

Transformations	轉換	說明	Block
Conditional Split Transformation	條件式分割轉換	routes data rows to different outputs.
Multicast Transformation	多點傳送轉換	distributes its input to one or more outputs.
Union All Transformation	聯集全部轉換	合併多個資料集
Merge Transformation	合併轉換	合併兩個已排序資料集
Merge Join Transformation	合併聯結轉換	使用 FULL、LEFT 或 INNER 來 JOIN 兩個資料集
Lookup Transformation	查閱轉換	The transformation that looks up values in a reference table using an exact match.
Cache Transform	快取轉換	The transformation that writes data from a connected data source in the data flow to a Cache connection manager that saves the data to a cache file.

Merge vs Merge Join vs Union All

• Merge Transformation 可以用來將多個已排序的輸入資料，合併成一個輸出資料，且合併後的整個資料也會排序。
• Merge Join Transformation 與 Merge Transformation 不同之處，在於其可以自行設定輸出的資料列，而不是全部。
• Union All Transformation 與 Merge Transformation 不同之處，在於其輸入不需要已排序，且其輸出也不會排序。

Conditional Split vs Multicast

這兩種轉換都會將一個輸入導向多個輸出， 兩者的差異在於： 「多點傳送」轉換會將每個資料列導向每個輸出，也就是每個輸出都與原來的輸入大小相同；而 「條件式分割」會將一個資料列導向單一輸出。也就是每個輸出內容都不會相同。

稽核轉換（Auditing Transformations）

Transformations	轉換	說明	Block
Audit Transformation	稽核轉換
Row Count Transformation	資料列計數轉換	資料列通過資料流程時計算其數目，並將最後計數儲存到變數中

自訂轉換（Custom Transformations）

撰寫自訂轉換。

Using Transformations

要使用何種 Transformations ，就將元件拖拉到 SSIS Designer 之中即可。詳細步驟請參考 Lab4 和 Lab5 。

Resolving Column References

如果在資料流程建立之後，資料來源中的資料表才被加入或刪除的欄位，這可能引發轉換元件參考到錯誤欄位。 過去，你必須一個一個欄位修改對應，現在可以使用［解析參考］快速解決對應問題。

Determining Appropriate ETL Strategy

ETL Strategy

查閱轉換（Lookup Transformation）

Lookup Transformation 元件是一個在 ETL 過程中很實用的元件，它可以用來查詢特定的 dataset 是否有符可條件的資料。 在使用這個元件時，有幾點要注意的：

區分大小寫

這個 Lookup Transformation 元件是區分大小寫的(case sensitive)。 你可以先加入 Character Map Transformation 元件，並利用 UPPER 或 LOWER 函式進行大小寫的轉換。

如何處理比對後的輸出

使用 Lookup Transformation 元件時，每次比對，都只會回傳第一筆符合條件的資料，這也是為什麼在比對前要先排序的原因。 它與 Merge Transformations 元件最大的不同處在於 Merge Transformation 回傳的是所有符合條件的資料。

比對後，會得到下列二個結果輸出：

1. 查閱比對輸出（Match output）：符合比對的結果集。
2. 查閱無相符比對輸出（No Match output）：不符合比對的結果集。

在 Lookup Transformation 元件的編輯功能中，你可以設定下列選項，針對「不符合的項目」來進行處理：

1. 忽略失敗：結果相當於使用LeftJoin，No Match資料會使用 Null 填塞，由 Match 端輸出。
2. 將資料列重新導向錯誤輸出：No Match資料會導向「錯誤輸出」，由 Error 端輸出。
3. 失敗元件：發生元件錯誤，產生 Error ，不輸出。
4. 將資料列重新導向無相符合結果輸出：No Match 資料會導向「無相符合結果」輸出。

處理不相符資料

如果選擇「將資料列重新導向無相符合結果輸出」，則 No Match 的資料將由「查閱無相符合結果」輸出。

2. 如果選擇忽略失敗，則 No Match 資料會與 Match 資料，都一起由「查閱比對結果」輸出。

如果二個結果你都想要保留，你可以使用 Union All Transformation 來合併二個結果。

除了以上做法，你也可以使用另外一個方法，而且效率會更好。 你可以在 Lookup Transformation 中將 No Match output 設定成忽略失敗，那麼不符合的比對的資料，其結果欄位就會被塞NULL值。 然後你可以在輸出結果下面再加入一個 Derived Column Transformation ，透過類似以下方法，來處理這些NULL欄位。

設定快取選項

• 完整快取：在查閱轉換執行之前，產生參考資料集，並將其載入快取。
• 部分快取：在查閱轉換執行期間產生參考資料集。 將參考資料集中具有相符項目的資料列，以及在資料集中沒有相符項目的資料列載入到快取。
• 沒有快取：在查閱轉換執行期間產生參考資料集。 沒有資料會載入到快取。

搭配 Cache Transform Transformation 使用，以提升效能

• It allows you to reuse the cache to reduce database load.
• It allows you to share the cache between lookups to reduce memory usage.
• You can do lookups against other (non OLE-DB) sources.

Sorting the Data

當使用 Merge Join Transformation 時，其輸入必須是已排序的資料，所以如果你是使用 TSQL 取得來源資料，你可以直接使用 ORDER BY 子句排序資料，另外你也可以使用 Sort Transformation 。

你必需將「排序轉換」插入到「合併」或「合併聯結」轉換之前。

你可以在[輸入與輸出屬性]的[排序輸出]中，找到 SortKeyPosition 屬性設定，如下圖位置：

• 0：預設值，不使用該欄位排序。
• 1：該欄位當做第１個排序欄位，且使用升冪。
• -1：該欄位當做第１個排序欄位，且使用降冪。
• 2：該欄位當做第２個排序欄位，且使用升冪。
• -2：該欄位當做第２個排序欄位，但使用降冪。
• ...

Set-Based Updates

Set-Based Updates 是什麼意思，簡單講就是針對一個資料的集合運作，白話一點就是一整批更新，而不是一筆一筆更新。 你可以參考網路這篇文章：Understanding “Set based” and “Procedural” approaches in SQL

在 SSIS 中，若你使用 OLE DB Command Transformation 來執行 SQL 以進行資料更新， 它會依據資料流中的資料，逐筆執行，所以僅適合資料筆數較小的案例，這方式也稱為 Procedural Updates。

如果資料筆數較大，通常會將這些要更新的資料先輸出到一個暫時資料表，然後搭配 MERGE T-SQL 類型的陳述式來達到 Set-Based Updates。

UPDATE C
SET 
	C.FullName = CU.FullName,
	C.EmailAddress = CU.EmailAddress,
	C.BirthDate = CU.BirthDate,
	C.MaritalStatus = CU.MaritalStatus,
FROM dbo.DimCustomers C
INNER JOIN stg.DimCustomersUpdate CU ON C.CustomerDwKey = CU.CustomerKey

• Integration Services 轉換