信德隨想

今天剛好有一個Case，可以當作範例介紹

先說明一下源由..

前幾天客戶說他們的資料有打錯，但已達上百筆查起來大約有7百多筆，要我協助幫他們修改，雖然說是不多，但要一筆筆手根據key更新資料的話，我看會瘋掉，因此我將需要修改的資料先撈到excel中，請他自己更新數值後，我再幫他更新回資料庫中

本來想說要寫個程式來更新，不過後來想說，用ssis不就得了，雖然執行起來有點小久，但總括來講，比寫程式還是快多了

因為就大約花了20分鐘的時間，做了這個範例，並也成功的更新了informix中的資料

怎麼做呢？我想也分享給大家好了，這是一種方式，但不一定是唯一的一種方式，我只是提供我實作的方式出來給大家參考，有任何更好的方式，歡迎留言討論哦..

首先我先拉一個”資料流程工作”

在”資料流程工作”按滑鼠右鍵，點編輯

從資料來源的工具箱中拉出一個”Excel來源”

一開始先新增一個excel連接管理員，選擇一個excel檔，之後在”excel工作表的名稱”中選擇一個sheet

以下是預覽的結果，在此先說明一下，最好先在excel中把資料格式先整理好，整理成像資料表那樣，就第一行為標題，以下都是單純的資料

不要有什麼大標題或按鈕啦，那種奇怪的東西在sheet上，不然會有問題

在資料行的地方，可以選擇你想要處理的欄位即可，當然在excel那裡就處理好的話，那更好，這裡就不用動了

接下來我拉出一個資料轉換的控制項，為什麼要用這個呢？因為我為了避免在執行時會出現什麼轉換錯誤之類，所以我習慣把欄位轉成字串類型輸出

要怎麼知道從excel抓出來的資料型態為何呢？

在”excel來源”按滑鼠右鍵，選擇”顯示進階編輯器”，之後再照下圖操作，就可以看到哪個欄位是什麼類型的囉！

在”資料轉換”按滑鼠右鍵選擇編輯，我把其中兩個欄位原本是數值型別的換成字串型別，並指定其長度

ssis會將轉換後的欄立的輸出別名自動改為該欄位名稱+"的副本”，如果你不喜歡的話，也可以自訂名稱

接下我們必須要新增5個變數，後面會用的到

chkbno,chkno是更新的where條件，資料類型為string

chkyn及setval是要被更新的欄位名稱，資料類型為string

rs是存放excel抓下來的資料暫存，資料類型為object

接下來再拉出一個”資料錄集目的地”，可能有人會說怎麼不用”ole db命令”直接執行就可以了

我也想啊，但我要更新的資料庫對象是informix，礙於driver，只是使用odbc，所以就不能用囉

在”資料錄集目的地”按滑鼠右鍵，選編輯，在元件屬性的頁籤中輸入  rs，這個是我自訂的變數

再切換到”輸入資料行”，我的畫面中，前二個欄位沒有選，因為這二個欄位是轉換前，所以其資料類型是數值，而後二個有”副本”字的資料類型是字串型別

這個畫面的順序要記住，因為後面我們在設定”foreach迴圈容器”時會有關係

接下來切換到控制流程，拉出一個”foreach迴圈容器”

在”foreach迴圈容器”上按滑鼠右鍵，選編輯

在集合的頁籤中，在 emumerator中選foreach ado 列舉值

然後在下方的ado物件來源變數選擇 rs 這個變數

接下來再切換到”變數對應”

這裡在變數的地方我新增了四個變數，索引的部份一定要按照前述的順序，否則會錯誤，而這個對應錯誤的話，會導致你之後在組sql 後值是不對的

接下來我們拉一個”指令碼工作”到”foreach迴圈容器”中

我們還要在新增一個變數，當作儲存組sql的內容

接下來在”指令碼工作”中按滑鼠右鍵，選編輯，切換到”指令碼”頁籤

在readonlyvariables填入唯讀變數，多個的話，用逗號區隔

在readwritevariables填入可變動內容的變數

之後再點選下方的設計指令碼按鈕，到程式編輯畫面

在main()這裡自行撰寫組sql的語法

讀取變數的方式為

Dts.Variables(“變數名稱”).Value.Tostring

下方的msgbox是我測試sql組起來的結果是對還是錯的測試，正式 run的時候，就會mark掉了，不然每跑一筆就會彈出訊息一次，很煩的

接下來再拉出一個”執行sql工作”

在”執行sql工作”上按滑鼠右鍵，選編輯

在一般頁籤中，設定如下

connectiontype:這裡選的是odbc，因為接的資料庫是informix

connection:要選擇你的共用連線名稱

sqlsourcetype:這裡要選”變數”

sourcevariable:這裡就是要選之前我們設定的變數updatesql

其他的就不用設定囉！因為我們只是要執行update語法，不回傳任何資料，所以在其他頁籤就不用再做設定了

終於大功告成了，接下來就執行看看囉！

下圖執行中的樣子

這次的教學範例雖然比較長，因為動作多及圖多 ，所以會覺得很冗長，其實熟的話，這樣的設定大約也只是幾分鐘就可以設定完了，看複雜度啦

也不是每個case都要那麼麻煩，給需要的人參考看看囉！

這樣做的話效能是有比較差一點，因為是使用了odbc且又是一筆筆更新，一定會較慢，不是批次更新成同一個值，如果批次更新就不用ssis用sql語法就可以了