SSIS 心得與小技巧

SSIS為Microsoft SQL Server裡Business Intelligence的一環
功能是整合各個不同來源的資料
以下環境處理使用SQL Server 2008
注意雖然SQL Server2012提供了轉換的功能來相容2008的檔案
但實際上還是很有可能會發生問題

 SSIS的檔案建立後會是dtsx檔
實際上裡面的內容是XML檔，由SSIS引擎進行實際操作
2008的版本有時候會發生問題是整個專案檔中作了變更
而dtsx檔卻沒跟著做正確變更，特別是在連線相關元件的連線改變時
有時候最好的情形甚至是將用到連線的元件砍掉重練
如果碰到設定應該沒問題卻總是收到SSIS的失敗訊息，那就應該考慮這個作法
假如遇到連線設定一直無法寫入連線元件(通常是連線的密碼)
可以考慮使用全域變數來規避，或是重新開啟SSIS試看看
在我接觸的經驗裡，連線設定是SSIS很弱的一個部分

SSIS開啟時會還原到上次處理時的內容
接著重新驗證載入內容裡面每一個處理過程，其中包含連線到裡面的資料來源
這樣往往使得SSIS在開啟過程花費大量時間而延誤處理
記得關閉SSIS前先關閉目前開啟的每一個dtsx檔

另外開啟SSIS的專案檔可以先做一個設定
上方工具列中的SSIS -> work offline
這樣可以避免SSIS在載入較大的檔案時卡在驗證的流程
當要用到取得網路上的資料時就取消work offine
雖然麻煩了些，但卻是省下時間的一招

雖然SSIS中有Script Component可以用來寫入程式運作
但在維護的考量上，部門鼓勵的處理方式是盡量用SSIS元件進行處理
使用元件能明顯的表達設計的意圖
雖然有時可能會較使用程式的方式來的麻煩，但帶來的好處應該是更多


以下紀錄曾碰過疑問的情境

1.重覆資料判斷處理

情境：想將重覆的資料判別出並寫進log，而不只是用sort工具將多出來的資料刪去
問題：如何判別資料是duplicate instances
解決：先利用derived column 新增欄位，再用aggregate元件裡的count物件來取得
            duplicate instance其數量，再將他寫入新欄位中，用conditional split處理

2.對應空值處理

情境：針對客戶給的資料做預防處理
問題：資料有可能不會有該欄位或該欄位無值
解決：conditional split裡 ISNULL(xxx) 和 TRIM(xxx) == "" 兩條件併用，
            TRIM()是預防給予的資料是數格空白

3.Thread

情境：欲建立物件來處理從data flow中經conditional split分支出來的資料
問題：物件在同時處理兩端不同走向的資料時可能會有multi-thread處理的問題
解決：先利用union all原件將所有要處理的資料先做統整，再用物件處理

4.Thread-2

情境：寫資料進error log碰到前端流程有太多錯誤資料要寫，使後端流程無法取得log檔
問題：手動加入lock控管機制也碰到lock object的取得也有thread上的問題
解決：經討論後直接濾掉前端的錯誤資料以避免整體流程發生錯誤
            實際上SSIS的線程管理是不常看到討論的一環，而且也容易有問題
            有這類需求時再考慮盡量避免掉資料存取衝突的可能，重新設計SSIS流程