五餅二魚工作室

當 SQL Server 2012 針對 SSIS 推出專案部署模式 ( Project Depolyment Model) 的時候( 可參考文章 小試專案部署模式( Project Deployment Model ) 和參數 ( Parameter )使用 )，當時在測試的時候發覺可以直接透過 Stored Procedure 進行呼叫，感覺上使用起來方便很多了。

因此如果要去執行一個 SSIS 的封裝，可以簡單的用以下的語法來進行 ( 以下的範例是執行在 SSISDB 內，目錄 Demo 下的 TEST1 專案，指定執行 Main.dtsx 的封裝 )

-- 載入專案並指定起始封裝
Declare @execution_id bigint
EXEC [SSISDB].[catalog].[create_execution] @package_name=N'Main.dtsx', @execution_id=@execution_id OUTPUT, @folder_name=N'Demo', @project_name=N'TEST1', @use32bitruntime=False, @reference_id=Null
Select @execution_id

-- 設定專案參數
DECLARE @var0 smallint = 1
EXEC [SSISDB].[catalog].[set_execution_parameter_value] @execution_id,  @object_type=50, @parameter_name=N'LOGGING_LEVEL', @parameter_value=@var0

-- 使用非同步方式執行封裝
EXEC [SSISDB].[catalog].[start_execution] @execution_id
GO

但如果今天執行的時候，登入 SQL Server 是採用 SQL 認證的情況下，則你可能就會收到以下的錯誤訊息

因此我們可以看一下  SSISDB 內的預存程序，而在看相關指令的時候，我們要先了解一下，在 SSISDB 內主要會有兩個結構描述( Schema )，分別是 catalog 和 internal。而 SQL Server 在設計上會將 catalog 結構下的一些 View , Stored Procedure 和 Function 開放給大家來使用；而在 internal 下的則是一些較底層的物件，則不建議我們直接來使用。

當我們在看 catalog 內的預存程序的時候，你會看到在每一個裡面，都可以在一開頭的地方看到類似以下的 SQL 語句

    EXECUTE AS CALLER
        EXEC [internal].[get_user_info]
            @caller_name OUTPUT,
            @caller_sid OUTPUT,
            @suser_name OUTPUT,
            @suser_sid OUTPUT,
            @caller_id OUTPUT;
          
        IF(
            EXISTS(SELECT [name]
                    FROM sys.server_principals
                    WHERE [sid] = @suser_sid AND [type] = 'S')  
            OR
            EXISTS(SELECT [name]
                    FROM sys.database_principals
                    WHERE ([sid] = @caller_sid AND [type] = 'S')) 
            )
        BEGIN
            RAISERROR(27123, 16, 1) WITH NOWAIT
            RETURN 1
        END
    REVERT
    
    IF(
            EXISTS(SELECT [name]
                    FROM sys.server_principals
                    WHERE [sid] = @suser_sid AND [type] = 'S')  
            OR
            EXISTS(SELECT [name]
                    FROM sys.database_principals
                    WHERE ([sid] = @caller_sid AND [type] = 'S')) 
            )
    BEGIN
            RAISERROR(27123, 16, 1) WITH NOWAIT
            RETURN 1
    END

每一個都會限制執行該預存程序必須要採用 Windows 認證的時候，才可以正常呼叫。但如果我們沒有辦法使用 Windows 認證的時候，是否有甚麼方式來啟動 SSIS 嗎 ? 這裡我們想到一個 借刀殺人 的方式，想透過 SQL Agent 來幫我們執行，因此我們利用以下的步驟來處理。

1. 我們先在 SQL Agent 下的作業選擇「新增作業」，這裡我們先設定名稱叫做 RUNSSIS

2. 在設定步驟的時候，我們要選擇類型為「SQL Server Integration Services 封裝」，而在下方則可以選擇，封裝來源是「SSIS 目錄」和我們來源的伺服器和所要的封裝。如果有特別要指定環境和參數，則可以到組態的頁籤下進行設定。至於其他排程的部分我們可以不用設定，確定設定好之後就可以進行儲存該作業了。

3. 此時我們就可以在使用預存程序 「sp_start_job」來執行剛剛所建立的封裝。

4. 在呼叫該封裝執行之後，則我們可以透過 「Integration Services 儀表板」來查看執行的狀況，從下表中我們可以看出封裝已經被正常執行完畢，而呼叫者會變成是 SQLAgent。

前一陣子剛好有看到百敬老師所寫的一篇「修改 SSIS 專案參數」，談到可以自己仿造 catalog 下面的預存程序，做一個自己的 Store Procedure，看起來也未嘗不是一種方式，只是我自己偏好的做法會是再做一個新的結構，這樣可以避免搞混，作法如下 :

1. 可以在 SSISDB 資料庫內，選擇「安全性」→「結構描述」，新增一個新的結構，這裡我改他取名為 mock

2. 將要仿製的預存程序選擇修改，將其改為是在 mock 的結構下

3. 將原本程序內去判斷是否是 Windows 授權帳號的程式段給注解掉，就可以建立該程序了

這樣你就可以使用這些 mock 結構下的預存程序，「看起來」就可以避開檢查，大部分的也都可以正常的執行。但是要特別注意一個地方，唯有 start_execution 這些會和使用到 SQLCLR Assembly 的預存程序，雖然可以正常呼叫，但會如同下圖一般，雖然有要 ISServerExec 去執行，但是卻沒有辦法執行任何一個封裝。

因此如果要做這些修改，會比較適合在一些像是環境、目錄、專案之類的修改，如果要執行的話，就還是需要採用 Windows 認證的登入或者是前面所介紹的透過 SQL Agent 來轉一手處理。

以上是自己一點測試心得，如果有朋友有其他更好的解決方案，也請您可以讓我知道一下，謝謝。