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

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

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

 

問題

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

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

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

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

 

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

 


 

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

image

 

image

 

image

 

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

<用 GUI 建立環境>

image

<用 T-SQL 建立環境>

image

 

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

image

 

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

<加入參考環境 Env1>

image

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

image

 

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

<執行封裝>

image

<執行結果>

image

 


 

完成前面的範例程式之後,且在環境上我們也建立好一組名稱是 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

<執行結果>

image

 

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 下面的變數值

image

 

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

<設定執行封裝>

image

<執行結果>

image

 


  

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

 

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

 

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

image

 

2. 在該封裝的設定上,我們選擇去參考不同目錄下的環境,並且將封裝的變數對應到環境的變數上

<設定參考>

image

 

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

image

 

3. 設定完之後,我們就可以在執行的時候,指定不同目錄下的環境,並且可以正常執行了。

image

 


  

SSIS 新的功能都還蠻實用的,如果還有在使用 SSIS 封裝部署模式的朋友,可以考慮改換成專案部署模式,可以讓您使用起來更加便利,也可以使用到新版本許多新的功能,很值得推薦給大家來使用。