SSIS-以變數的Sql當作RunTime的實際Sql

SSIS-以變數的Sql當作RunTime的實際Sql

會用到這樣的方法,可能性很多,例如你用是非微軟牌的sql server,你用的是oracle,sybase資料庫,且前提是有for ole db provider的才行

不然的話,你還是得要用datareader來源的方式,但這個冏狀只會在ssis2005版發生,ssis2008可以使用ado.net來源的方式解決

但這不是我今天要介紹的重點,我今天要舉的例子是

假設你的sql語法,是要用組sql的方式動態查詢,亦或是你的查詢欄位不變,但因子查詢會要用到where條件,且這個where條件又要動態給入的話,就比較適合用這種方法

雖然會麻煩些,但是可以解決問題的方法之一,可以參考看看

案例說明:假設我是用oracle,但ssis的ole db不支援 where的變數參數,因此我要再前面一開始就先組好sql給一個變數,然後把這個變數當作我下查詢命令

首先拉一個”指令碼工作”

clip_image001

然後設一個變數

clip_image002

sqlstr:這個變數是用來放組sql後的字串

另外在”值”的地方,請先輸入一段你自己想要查詢的sql 語法

ex:

SELECT  top 1 訂單號碼, 客戶編號, 員工編號, YEAR(訂單日期) as 訂單日期 FROM              訂貨主檔 WHERE          (YEAR(訂單日期) = 1996)

這個就是到時候真正執行的查詢語法,只不過你可以先只查第一筆,因為必須在”資料流程”的”oledb 來源”可以有table schema

接下來,請在”指令碼工作”按滑鼠右鍵

若是ssis2008

在scriptlanguage的地方,可以選擇你習慣的語法,有C#及vb可以選,我們選vb比較跟ssis2005一致,當然你也可以選c#

若是ssis2005,則只有vb,且是固定的,沒得選

畫面中的readonlyvariables指的是只能唯讀的變數

畫面中的readwritevariables指的是可以讀寫的變數,我們在此填入sqlstr,因為之前我們的變數是設這個

接下來請按”編輯指令碼”的按鈕

clip_image003

接下來會開啟程式碼編輯器,請在Main()輸入以下程式碼

‘我先宣告一個年的變數,當作我的where條件

Dim qyear As Integer = 1997

‘以下的sql跟上面所寫相同,只是我拿掉top 1 ,並把過濾條件變成接變數,另外要注意的事,如果你是要的接應該是字串,請應改為=’”+qyear+”’的方式,前申接單引號

Dim newsqlstr As String = "SELECT 訂單號碼, 客戶編號, 員工編號, YEAR(訂單日期) as 訂單日期 "
newsqlstr += "FROM              訂貨主檔 WHERE (YEAR(訂單日期) = " + qyear + ") "

‘sql 組合好後,寫回變數中
Dts.Variables("sqlstr").Value = newsqlstr

我上述的程式碼,是用在組sql字串,如果你的是oracle的procedure,或其他資料庫的sql語法,也可以用類似的方式組字串,來達到動態sql的方式

接下來拉出一個”資料流程工作”控制項

clip_image004

接下來在'”資料流程工作”中按滑鼠右鍵選編輯

拉出一個”oledb 來源”

clip_image005

在”ole db來源”按滑鼠右鍵選編輯

在寫這篇文章的同時,我是使用sql server 2008的中文試用版,但有發現一個bug,對於中文的資料庫名稱,好像沒有辦法在ssis中使用”來自變數的sql命令”這個方式

但如果我把資料庫名稱改成英文的就可以了,希望在sql server 2008出來時,能處理掉這個bug

在資料存取模式中選擇”來自變數的命令,在變數名稱,選擇我們自訂的全域變數名稱sqlstr

clip_image006

點選預覽,若有看到資料的話,就表示是ok的

接下來點選”資料行”頁籤,如果有看到類似如下的畫面的話,那就是有對應囉!欄位不一定會跟我的一樣

clip_image007

接下來就看各位後續要怎麼接資料囉!

使用變數當作資料來源的用法,大致上介紹到此,有問題的歡迎留言討論囉!