[SSIS]SQL Task帶參數呼叫預存程序並取回Output參數

最近有一個需求從SSIS帶參數呼叫預存程序,同時將Output參數取回來用c#處理,筆記步驟!

 

 

1.首先在SQL Server上建立一個帶參數的預存程序,程式邏輯是如果輸入口令是1就是正確的,否則錯誤,

正確及錯誤都會輸出回應碼rsp及訊息內容error


CREATE
    PROCEDURE [dbo].[usp_SSIS] 
         @t                     int,                        --輸入值: 
         @Rsp                   varchar(1) output,          --回傳值: 正常0/錯誤1
         @Error                 varchar(500) output    	    --回傳值: 錯誤訊息
    AS
BEGIN TRY

IF @t = 1
BEGIN
    SET @Error = 'OK';
    SET @rsp = '0';
END 
ELSE
BEGIN
    SET @Error = 'Wrong Number';
    SET @rsp = '1';
END

END TRY 
BEGIN CATCH
    SET @ERROR = ERROR_MESSAGE();
    SET @Rsp = 1;
        IF XACT_STATE() <> 0 ROLLBACK;
    RETURN
END CATCH

 

2.接著在SSIS新增一個Package參數t,先設定值為1

3.呼叫預存程序,我們選擇SQL工作

左鍵點兩下"執行SQL工作",跳出工作編輯器後依序輸入

  • Connection Type: ADO.NET
  • Connection: 連接字串(看環境調整)
  • SQL Statement: dbo.usp_SSIS
  • IsQueryStoredProcedure: True

在參數對應上依序新增輸入及輸出參數,這邊要注意@error及@rsp方向要選Output

4.這邊模擬用指令碼工作來處理Stored procedure回傳結果。

新增指令碼工作

輸入ReadOnly參數 User::error,User::rsp,$Package::t,輸入完畢後按下編輯程式碼。

在Main的段落中輸入以下程式碼

準備一個Message Box來顯示Stored procedure回傳結果

public void Main()
{
    // TODO: Add your code here

    Dts.TaskResult = (int)ScriptResults.Success;

    string rsp = Dts.Variables["User::rsp"].Value.ToString();
    string error = Dts.Variables["User::error"].Value.ToString();
    MessageBox.Show(string.Format("rsp = {0},error = {1}", rsp, error));
}

輸入完畢後關閉VisualStudio程式碼視窗,指定碼視窗按下確定。

 

5.準備一個郵件工作來顯示預存程序回應碼

設定完mail連線資訊後,MessageSource輸入

"回應碼:"+@[User::rsp]+" 訊息內容:"+@[User::error]

6.把流程接起來然後測試

因為目前Package參數t是正確值1,所以視窗顯示rsp=0, error = ok

也收到郵件

7.把Package參數t設置為2試試看!

視窗跳出rsp=1 error=wrong number

Mail訊息也顯示

 

參考:

Parameters and Return Codes in the Execute SQL Task

How to display variable in ssis script task

Stored Procedure Return Values and Output Parameters in SSIS