SSIS-執行 SQL 工作-回傳單一資料列

SSIS-執行 SQL 工作-回傳單一資料列

這次介紹的是ssis中的”執行 SQL 工作”控制項

這個控制項在ssis中也算是經常用到的一個重要元件

有些工作可以透過這個控制項使用sql指令來快速執行

例如你每次在塞資料到table前,要先清空目的資料表,可以用這個控制項執行delete的sql語法

亦或是如果你的資料來源,沒有辦法在”資料流程工作”裡面做的話,那就只好使用這個控制項,搭配”foreach迴圈容器”來執行資料流程

例如IBM的DataBase之前我在試的時候,在資料流程工作中的oledb都沒有辦法讀資料,可能因為當時還沒有支援ssis的oledb

因此我嘗試成功過的方式就是使用”執行 SQL 工作”搭配”foreach迴圈容器”的方式將 db2的資料寫到sql server中

本次範例先介紹使用”執行 SQL 工作”後傳回單一資料列的作法,例如回傳查詢的筆數(count),當然還可以有更多的應用,只是這次先介紹這個部份

先列出這次範例table中的資料內容,共3筆

clip_image001

接下來到ssis中先拉出一個” SQL 工作元件”控制項

clip_image002

之後我們先建立一個變數:test,到時候用來接執行結果用

clip_image003
clip_image004

在SQL 工作元件上按滑鼠右鍵,選擇編輯

clip_image005

在”一般”的頁籤中,我們要先連接一個資料來源,在此我們先選擇ole db,其餘的就看實際狀況,看倌們自行選擇

clip_image006

在connection中,請選擇一個連線,下圖中的demodb是我自己建立的一個共用連線,不知道怎麼建立共用連線嗎?請參考SSIS-建立連線資料-PART1這篇文章的教學

clip_image007

在sqlsourcetype中,我們使用預設的值-“直接輸入”

這個項目中有3個選項

1. 直接輸入:顧名思義就是直接將sql語法打上去

2. 檔案連接:如果你的sql語法是自於一個實體檔案的話,也可以選擇這個項目,之後就會要你選擇檔案囉!

3. 變數:如果你的sql是動態的,依情境的不同而組合出來的,那就適合用這個,要用這個的話,要先在ssis中建立一個變數,並先給定一段sql,也就是預設值的意思,否則一執行就不過了
不懂嗎?ㄟ….這個不是這次的重點,下次再介紹

clip_image008

接下來在sqlstatement中輸入你要執行的sql語法

clip_image009

選擇這次執行後的動作,我們選擇”單一資料列”

這個項目中有3個選項

1. 單一資料列:回傳一筆資料,如果你查詢的sql語法中有要回傳多個欄位的值也是可以,但只能回傳一筆,因為我們是選擇”單一資料列”

2. 完整結果集:這個選項的話,會回傳整個資料集,如果你查詢的sql語法,是需要回傳多筆資料的話,那就要選這個

3. XML:如果你查詢後的回傳的內容是XML結果,那就要選這個,不過老實講,小弟本身目前也還沒有做過回傳結果是XML的,所以也不知道要怎麼做後續設定

clip_image010

Timeout的選項及codepage這個選項就先用預設值吧,除非你有特別的需要,才需要變動,例如,查詢時候會需要比較久,那就改一下timeout的值吧

clip_image011

下方有三個按鈕

1. 瀏覽:這個是選擇你的sql語法的檔案

2. 建立查詢,可以開啟sql 視覺化查詢視窗,用拖拉的方式完成sql語法的建立,但如果不是透過oledb的方式的話,是不能選擇的

3. 剖析查詢,只是檢查sql語法是否正確,但其實這個只是比較適用於檢查sql來源為sql server而已,其他的檢測,其實是有問題(我個人覺得啦)

clip_image012

接下來在參數對應的部份,我們在這個範例中先不做任何的設定

clip_image013

在結果集的部份我們加入一個變數

1. 結果名稱:這個請依照你所定的欄位位置來設定,這裡我0,是因為結果集回傳的只有一欄的資料,這裡解釋一下
例如你select empid,empname,sex from empolyee,你查詢了3個欄位,則empid為第0欄,empname為第1欄,sex為第2欄,因為你要指定順序對應欄位,這樣有比較了解嗎?

2. 變數名稱:這裡請選擇你所對應的變數名稱

clip_image014

上面的設定設完之後,我們再拉出一個指令碼工作來呈現結果筆數

clip_image015

在指令碼工作按滑鼠右鍵,選編輯,在readonlyvariables中填入之前設定變的變數名稱”test”

clip_image016

接下來按”編輯指令碼”

我們在main()這個主function中,輸入這一行程式碼

MsgBox(Dts.Variables("test").Value.ToString)

clip_image017

到此全部就設定完成囉!

我們來執行看看吧

clip_image018

執行結果是對的囉!共3筆,畫面中的”指令碼工作”控制項呈現黃色是表示執行中,因為我的畫面彈出了msgbox,但這比較適用於debug或demo用

實際執行時,不適用彈出msgbox,應該是接續做後面的動作

ok!先介紹到這裡,下次有空再介紹,關於回傳結果集的部份

有任何意見,請留言再討論一下囉!

‧覺得文章不錯請給我一個『讚』作為鼓勵喔!