最近有一個需求從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