[SQL][SSIS]SSIS 專案部署時，是否可以複製環境和跨專案與跨目錄使用同一組環境 ?

2015-01-12

3304
0
SSIS
2020-02-20

[SQL][SSIS]SSIS 專案部署時，是否可以複製環境和跨專案與跨目錄使用 ?

在使用 SSIS 2012 之後的專案部署，新的「參數( Parameters )」和「環境( Environments )」的搭配，很多時候都非常的便利。而在教課的時候，大家也都該功能非常有興趣，當中有朋友提到幾個問題，想說這個範例來說明一下。

問題

1. 當設定好一組環境之後，要怎麼複製該設定產生一組新的，免得當有名稱多的時候容易搞錯 ?

2. 當設定好環境之後，那麼該組環境是只能給特定的封裝使用，還是同個目錄下的都可以使用 ?

3. 當有不同的目錄的時候，是否可以跨目錄來使用相同的環境設定 ?

4. 參數是屬於封裝還是專案的設定 ?

基本上目前在 SSMS 的管理介面上，並沒有直接提供複製的功能，但我們仍然可以自己手動下指令來做一些處理，就可以做到類似複製的功能。而環境在使用上沒有特別的限制，只要在同一個 SSISDB 內的，都可以相互使用，也就是您可以是在不同目錄或專案下，使用相同的環境。你可以針對專案和封裝上都設定參數，因此你在執行封裝的時候，就要同時給專案參數和封裝參數。

1. 首先我們先建立一個測試環境，先建立一個封裝，這個封裝有四個參數，並且貼上一個指令碼元件，將這四個參數傳入到該元件內，元件內利用 Messagebox 來顯示傳入值。

2. 將該專案部署到 SSISDB 內的目錄 Clone 之下，並且建立一個「環境」為 Env1，這部分可以使用 SSMS GUI 介面去建立，或者是使用指令去建立都可以。

<用 GUI 建立環境>

<用 T-SQL 建立環境>

3. 設定該環境 Env1 下的變數，這裡我們先取名為 SetBoolean、SetDateTime、SetInt、SetString，並且將這四個變數都賦予我們想要給他的值。

4. 接著在我們所上傳的這個專案的 Main 封裝上，選擇「設定」去參考環境 Env1 ，並且指定「環境」的變數對應到專案封裝的參數

<加入參考環境 Env1>

<設定封裝參數對應環境下的變數>

5. 執行該專案封裝，並且設定使用環境 Env1，如果設定無誤的話，正常來說應該會有下圖的結果。

<執行封裝>

<執行結果>

完成前面的範例程式之後，且在環境上我們也建立好一組名稱是 Env1 的設定，接下來我們會再建立一個 Env2，並且把相同設定複製到 Env2，這樣就不用設定一堆變數名稱且擔心會有打錯的狀況，只要更改不同的設定值就好了。做法如下 :

1. 利用 SSMS 的 GUI 或者是 T-SQL 建立一個新的環境，這裡我們先用指令來建立一組新的環境在目錄 Clone 之下

EXEC [SSISDB].[catalog].[create_environment] @environment_name=N'Env2', @folder_name=N'Clone'

GO

2. 透過查詢「internal.environments」的資料表可以看到我們有兩組環境，這裡我們要注意一下地方，就是這兩個環境的代號，分別是 Env1 編號是 1，而 Env2 的編號是 2；而透過查詢「internal.environment_variables」的資料表可以看到在 Env1 下有四個變數，以及變數的設定資訊。

<指令>

select * from internal.environments

select * from internal.environment_variables

<執行結果>

3. 接下來我們用暴力的方式來將環境編號 1 在 internal.environment _variables 的資料複製一份給環境編號 2，因此我們使用以下的語法

DECLARE @Source_Environment int;
DECLARE @Target_Environment int;

-- 設定來源環境編號是 1
SET @Source_Environment = 1;

-- 設定目的環境編號是 2
SET @Target_Environment = 2;

insert  into  internal.environment_variables
	( environment_id,name,description,type,sensitive,value,sensitive_value,base_data_type  )
  select  @Target_Environment,name,description,type,sensitive,value,sensitive_value,base_data_type  
  from  internal.environment_variables 	
    where  environment_id = @Source_Environment

4. 當我們開啟 Env2 的設定之後，就可以看到透過前面的步驟，已經將原本的 Env1 下面的變數都已經複製到 Env2 了，因此接著我們就可以透過 SSMS ，來改變新複製出來的環境 Env2 下面的變數值

5. 當我們再次執行該封裝的時候，此時指定使用環境 Env2，就會發現他已經可以按照我們所設定的新環境變數值去執行了。

<設定執行封裝>

<執行結果>

在前面的說明中，我們利用直接寫入 internal.environment_variables 資料表的方式來複製環境變數，之所以會用這樣的方式而不使用 catalog 相關的預存程序去處理，一來是這樣會比較快，二來是因為如同前面一篇「當使用 SQL 認證的時候如何呼叫 SSIS 執行 ?」所遇到的問題，執行那些預存程序都會檢查是否是使用 Windows 認證登入的。如果您沒有需要考量這樣的問題，也是可以透過 [create_environment_variable] 的預存程序，去一個一個建立環境變數，會是比較正統的方式。

透過前面的範例我們展示了如何複製環境內的變數，接下來我們展示可以跨目錄來使用環境

1. 在完成前面的範例，我們又在做一個新的封裝，這個封裝我們我們刻意放到另外一個目錄 Demo 下面，並且該目錄下是沒有設定任何「環境」。