SSIS中的容器和數據流—數據轉換（Transformations）

對數據流來說按照需求將數據轉換成需要的格式是數據操作中的一個關鍵的步驟。例如想要得到聚合排序后的運算結果，轉換可以實現這種操作。和SQL Server 2000 DTS完全不同，這些操作不需要編寫script，所有的運算都在內存中進行。添加一個數據轉換之后，數據直接轉換成想要的格式然后傳遞給下一個步驟，由于是在內存中完成運算操作的，不需要在數據庫中建立對象來存儲這些操作函數。但是當需要處理大批量的數據時，還是需要建立存儲表或其他對象來處理的。

在data flow面板中拖放一個轉換，鼠標點住然后拖放綠色連線到一個目的Destination，然后就可以雙擊并編輯這個轉換。這部分將從最基本的功能開始講解轉換，下一部分將會做一些實驗操作，并在實際盛傳環境中來使用它。還有一些更高級的轉換著這里不會講解，把他們放在下一個隨筆中講解，這些高級的轉換包括：

• 修改Dimension
• 透視和逆透視Pivot and Unpivot
• 行數
• 導入導出列
• 術語提取和查找
• 模糊分組和匹配
• 數據挖掘
• OLE DB命令

其中一些轉換不是很復雜，只是在SSIS沒有UI支持，要想使用它們需要使用Visual Studio中的高級編輯器。

聚合Aggregate

聚合轉換可以像T-SQL中的函數GROUP BY, Average, Minimum, Maximum, 和 Count一樣對數據進行聚合運算。在圖4-13中可以看到數據以OrderDate,ProductLine分組，對LineTotal做求和操作。這樣產生了三列新的數據，供其他操作。

圖4-16

在聚合操作編輯器Aggregate Transformation Editor，首先選擇需要進行聚合操作的列，然后選中的列將會出現在下面的一個表里面在Output Alias列里面輸入輸出列的別名。例如如果想輸出每個訂單的總量，這里修改為SubTotal，這樣可以更容易的識別出這一列的目的。最重要的以個設置是在Operation這一列選擇聚合操作。它有一面一些選項。

• Group By：將數據按照某列分組
• Average：對數據列求和操作
• Count Distinct：對一組數據中非空行計算行數
• Count Distinct：對一組數據中的非空行計算非重復的行數
• Minimum：對一組數據中的數字列求最小值
• Maximum：對一組數據中的數字列求最大值
• Sum：對一組數據中的數字列求和

點擊Advanced標簽界面，在這個界面可以編輯轉換輸出。在Advanced界面可以輸入聚合計算的名字，產生一個新的列。在Advanced標簽界面，最關鍵的選擇項是設置轉換時的緩存，使它在一個合適的水平。例如較低水平設置為500000條，中等水平設置為5000000條，較高水平設置為25000000條。也可以使用Number of Keys屬性設置具體數值。Auto Extend Factor屬性設置轉換可以使用的內存量，默認的值是25%，也可以設置其他選項保證RAM占用量。Warn On Division by Zero單選框用來處理求平均值時被除數是0，如果沒有選擇，轉換失敗將不會給出任何提示。

審核轉換

審核轉換允許對數據流添加審核審核數據，以往使用HIPPA和Sarbanes-Oxley (SOX)時，必須跟蹤誰在什么時插入數據，審核轉換可以實現這種功能。例如要跟蹤那一個task向表里插入數據，可以在審核轉換中添加相應的列。在Output Column Name列中輸入想要審核的列，在Audit Type內選擇審核類型如圖4-17，可以選擇的類型有：

• Execution Instance GUID:GUID標示是那一個package執行的插入操作
• PackageID：package的唯一標示
• PackageName：package的名字
• VersionID：package的GUID的版本
• ExecutionStartTime:package開始執行的時間
• MachineName：package所在的機器名
• UserName：啟動package的用戶
• TaskName：數據流中task的名字
• TaskID：包含轉換task的Data Flow Task的名字

圖4-17

字符映射

特征映射轉換如圖4-18展示數據流中列的特征，它的編輯界面很簡單，只有一個標簽界面，點擊要進行映射的列，可以選擇需要添加新列或需要更新的列?？梢栽贠utput Alias列中給原來的列一個別名，選擇要進行的操作，可以進行的操作類型有：

• Byte Reversal：逆轉數字位的順序，例如數據0x1234 0x9876的逆轉結果是0x4321 0x6789，這種操作和LCMapString，LCMAP_BYTEREV選項有相同的操作結果
• Full Width：將半長字符轉換成全長
• Half Width：將全長字符轉換成半長
• Hiragana：將日語中的片假名轉換成平假名
• Katakana：將日語中的平假名轉換成片假名
• Linguistic Casing：使用區域語言規則
• Lowercase：轉換成小寫字符
• Traditional Chinese：將簡體中文轉換成繁體中文
• Simplified Chinese：將繁體中文轉換成簡體中文
• Uppercase：將字符轉換成大寫

條件分割

根據條件分割數據是一個在數據流中添加復雜邏輯的方法，它允許根據條件將數據輸出到其他不同的路徑中。例如，可以將產品中總數量超過500的輸出到一個路徑，少于500的輸出到另一個路徑。如圖4-19?？梢詮纳厦娴膶傩越Y構中拖放一個列或者代碼段，然后根據邏輯重命名而不是使用默認值Case1，還可以編輯輸出列的名字。

圖4-19

可以使用表達式來讀取字符數據，設置判斷條件，例如下面表達式判斷城市名字的第一個字符是F，SUBSTRING(City,1,1) == "F"。將這個轉換連接到下一個其他轉換的時候會彈出一個對話框提示選擇數據輸出路徑，如圖4-20.在這個圖中，可以看到有兩個選擇，GoodSales輸出到一個路徑，其他輸出到另一個路徑。還可以新建其他的路徑以供選擇。

圖4-20

如果有兩個選擇，一個默認選擇適用于其他情況，路徑如圖4-21

圖4-21

復制行

復制行轉換時一種很簡單的轉換，它將某一列的數據復制一份克隆。這種操作在執行某些精確的轉換之前先創建一份拷貝時非常有用。然后將可以對照拷貝數據修正源數據。雙擊打開編輯界面，選擇要拷貝的列，并重新命名。注意：有些轉換允許將一列數據復制到內在列里。

數據轉換

數據轉換執行類似于T-SQL中的函數CONVERT或CAST的功能。數據轉換的編輯界面如圖4-22，選擇需要轉換的列，在DataType下拉列表中選擇需要的數據類型。Output Alias欄內設置輸出時使用的別名。

圖4-22

數據挖掘請求

數據挖掘請求運行數據挖掘請求，并將結果輸出到數據流。它還可以添加一些預測新列，一些應用場合如下列舉：

• 根據已知的一些列，例如子女個數，家庭收入，配偶收入預測產生一個新列：這個人是否擁有住房
• 根據客戶購物卡預測客戶的購買意向
• 可以填充用戶的調查問卷上沒有填寫的空白欄

派生列

導出列任務可以從從另一個輸出中創建一個新的列。例如，是訂單數量和訂單價格相乘導出一個新的列訂單總價格,如圖4-23也可以使用ISNULL函數填充當前時間或者某一天的空白數據。這是五個可以代替T-SQL代碼編程的task之一。

圖4-23

輸出列

輸出列任務可以沖數據流中輸出一副圖片或者一個文件。和其他的轉換task不同這種轉換不需要一個Destination。如圖4-24打開編輯界面，選擇一個包含文件的列，再選擇一個文件輸出路徑。

圖4-24

另外一個選項這只再次執行task的時候文件被刪除或覆蓋，Allow Append選項輸出是否累加到一個已經存在的文件中。如果選擇Force Truncate選項，會覆蓋掉已經存在的文件。Write BOM選項設置如果數據類型是DT_NTEXT是否寫入字符順序符號。如果沒有設置Append和Truncate選項，task將執行錯誤，錯誤信息類似于下面：

Error: 0xC02090A6 at Data Flow Task, Export Column [61]: Opening the file

"wheel_small.gif" for writing failed. The file exists and cannot be overwritten. If

the AllowAppend property is FALSE and the ForceTruncate property is set to FALSE,

the existence of the file will cause this failure.

輸入列

輸入列和輸出列是一個正好相反的task，他可以將圖片和文本文件輸入到一個數據行中，他們的編輯設置也是類似的。

先到這里，剩下的轉換任務下次隨筆在接著再說。