修改 SSIS 專案參數

好友 email 了個有趣的問題,為了保密稍作修改:

 

…客戶根據內部管理規範,資料庫的連線字串會定期變更

…想利用SSISDB裡的SQL指令來更新專案參數

所以我們的思路是寫一隻COMMAND程式先抓到連線字串(我們的連線字串權限是SA層級)

在利用這連線字串連結SSISDB在更新專案參數在執行

但是卻發現SSISDB不允許利用SQL Server帳號密碼存取,只能夠利用Windows整合認證存取

…不允許執行批次的執行身分具有資料庫存取權限

想問的是除了透過SSISDB的SQL指令外

還有甚麼方式可以變更專案參數??

DTEXEC能嗎? MSDN文件上寫的很模糊,好像是能但是沒有太深入的資訊…

 

 

 

 

 

我的感覺如下:

1. 連接設定不一定要靠專案參數,你可以透過 Script Task 設

2. DTEXEC 可以設定專案參數 /Par[ameter]

3. 若用 SSIS Catalog 環境對應專案參數可以:

  1. 若變數內容沒加密,或許你可以直接修改  [internal].[environment_variables] 系統資料表
  2. 直接檢視 [SSISDB].[catalog].[Set_environment_variable_value] 系統預存程序可以看到它是檢查 caller 的帳號類型,若非 Windows 就跳掉,這在執行 SSIS 封裝時合理,因為實際是外部程式執行 SSIS,所以該程式需要 Windows 帳號。但我不知道為何改變數內容也需要 Windows 帳號,所以依照原 SP,但 mark 掉以下的檢查,就可以正常執行了

    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, 8) 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, 8) WITH NOWAIT
            RETURN 1
    END

 

簡單測試一下,可行

EXEC [SSISDB].[catalog].[Set_environment_variable_value] @variable_name=N’Connection’, @environment_name=N’myEnv’, @folder_name=N’myFolder’, @value=N’Data Source=.;Initial catalog=Database1;Provider=SQLNCLI11;Integrated Security=SSPI’
EXEC [SSISDB].[catalog].[mySet_environment_variable_value] @variable_name=N’Sensitive’, @environment_name=N’myEnv’, @folder_name=N’myFolder’, @value=1234

當然,若用戶允許一個 Windows 帳戶來 impersonate,然後執行該預存程序或許也是可行

發表迴響

在下方填入你的資料或按右方圖示以社群網站登入:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / 變更 )

Twitter picture

You are commenting using your Twitter account. Log Out / 變更 )

Facebook照片

You are commenting using your Facebook account. Log Out / 變更 )

Google+ photo

You are commenting using your Google+ account. Log Out / 變更 )

連結到 %s

%d 位部落客按了讚: