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

2016-07-23

3285
0
SSIS

最近有一個需求從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.準備一個郵件工作來顯示預存程序回應碼