ssis sql server ETL

SSIS時序容器與容器交易屬性介紹

陳仲和 2019/12/30 23:49:47
135

Microsoft SSIS時序容器因為沒有像另外兩個容器(For迴圈容器與Foreach迴圈容器)有流程設計的行為,故容易在設計SSIS package(封裝)時被忽略棄用,近日在某一專案開發時,發現有同仁善用此元件去做更細微的層級交易管理,故興起研究此一元件的使用方式與時機,並介紹給有需要的朋友。

時序容器並無自訂的使用者介面,使用方式也很簡單,就是把需要當作同一個群組的Task拖曳到裡面,然後依控制流程關聯好上下關係即可,一般來說,與沒有被群組化的Package比較,會被認為只是排版上比較美觀,比較有分群的概念而已,或是在測試偵錯階段時,可以把此容器依測試目的打開或停用(因為是容器的關係,故可以對容器內的相關Task做一致性的設定),方便偵錯時不要被干擾,除此之外,也想不出有什麼作為,故對此元件都是忽視不用。

但事實是時序容器的功能可不只如此,搭配上交易屬性(TransactionOption)的設定,將會使此元件的運用多了起來,在此需要先對交易屬性的參數做個說明,裡面提到的交易是專指對資料庫資料的行為,例如新增、刪除修改的行為,而回是指transaction rollback。

參考Microsoft文件說明摘錄如下:

Integration Services 提供了三種設定交易的選項:NotSupported、Supported 和 Required。

1.Required 指出容器會啟動交易,除非其父容器已經將其啟動。 如果交易已經存在,則容器會聯結交易。 例如,如果未設定為支援交易的封裝包括使用 Required 選項的「時序」容器,則「時序」容器會啟動其自己的交易。 如果封裝設定為使用 Required 選項,則「時序」容器會聯結封裝交易。

2.Supported 指出容器不啟動交易,但會聯結其父容器啟動的任何交易。 例如,如果具有四個「執行 SQL」工作的封裝啟動交易,且全部四個工作都使用 Supported 選項,則任何工作失敗時,都會回復「執行 SQL」工作執行的資料庫更新。 如果封裝未啟動交易,則四個「執行 SQL」工作不會由交易繫結,且只會回復由失敗之工作執行的資料庫更新。

3.NotSupported 指出容器不啟動交易或聯結現有的交易。 父容器啟動的交易不會影響已設定為不支援交易的子容器。 例如,如果封裝設定為啟動交易,且封裝中的「For 迴圈」容器使用 NotSupported 選項,則「For 迴圈」中的工作一旦失敗將無法回復。

如上圖,TransactionOption的設定是在屬性設定介面上做選擇,但實際上如何運作端看字面上的說明實在不容易理解,故我們還是準備幾個範例來做實驗與說明,實驗如下

情境環境假設:
建立三個Table, 分別是Store, Product與Stock, 其中Store table裡面有商店01, 02兩家店, 然後假設先增加商品到Store 01, 再增加相同的商品到Store 02, 但因為故意讓語法錯誤來測試其資料Insert or rollback的狀態, 故一樣新增同樣的商品到Store 01, 因為Stock Table的PK設定為StoreID+ProductID, 故相同的PK Data Insert會導致錯誤,藉此來確認實際執行狀況。


上圖可以看到目前Stock Table目前是還沒有資料的, 之後預計在SSIS的封裝裡加上二個執行SQL工作的Task, 裡面放的SQL語法是在Table Stock裡增加Store 01的商品數量資料, 藉由設定不同的TransactionOption來確認資料Commit與RollBack的狀態。

TransactionOption設定狀況試驗
情境1.時序容器:Required, 執行SQL工作1(商店1進貨):Supported, 
執行SQL工作2(商店2進貨): Supported
         執行畫面:

可以看到在此時序容器裡,  雖然第一個SQL Task(商店1進貨)的Insert Data是成功的, 但因為第二個SQL Task(商店2進貨)的Insert Data是失敗的,故整個時序容器是失敗的,我們來檢查一下實際上在Table Stock裡, 商店01的資料是否有被Insert進去?

       執行結果:

由上圖可以知道,雖然
第一個SQL Task(商店1進貨)的Insert Data是執行成功,但因為此案例是時序容器發起一個Begin Transaction(Required),裡面兩個Task是本身沒有發起Transaction,而是關連上層的交易(Supported),故整個交易是要時序容器裡所有成員都是成功的才會Commit, 所以在此案例Insert是被Rollback, 故連商店1進貨都沒有寫入成功。

情境2.時序容器:supported, 執行SQL工作1(商店1進貨):Required, 
執行SQL工作2(商店2進貨): Not Supported
        執行畫面:

畫面與情境1是一樣的結果,讓我們再來看看Table實際的狀況
       執行結果:

此一結果與情境一有所不同,第一個SQL Task(商店1進貨)的Insert Data是執行成功,且資料是被寫入到Table的,原由是在這裡是由第一個SQL Task發起一個Begin Transaction(Required),而不是由時序容器發起,然後在第二個SQL Task(商店2進貨)裡又設定與第一個SQL Task脫鉤(Not Supported),故即使在執行畫面是失敗的,但因為第一個SQL Task(商店1進貨)是一個獨立的Transaction交易,故還是有Commit成功,資料還是寫入到Table Stock裡面

情境3.時序容器:Suppored, 執行SQL工作1(商店1進貨):Required,  執行SQL工作2(商店2進貨): Required
   執行畫面:


畫面與情境1, 2是一樣的結果,讓我們再來看看Table實際的狀況
       執行結果:

此一結果也是第一個SQL Task(商店1進貨)的Insert Data是被寫入到Table的,原由是時序容器與情境2一樣,本身沒有發起Begin Transaction(Supported),而是由裡面兩個SQL Task獨立發起Begin Transaction(Required),故個別執行交易,資料還是寫入到Table Stock裡面。這裡要注意的是兩個SQL task的連接線屬性LogicAnd要設定false才不會干擾此情境實驗執行。false是表示此控制流程有上下關係,但條件約束是不相關的。

情境4.時序容器:Required, 執行SQL工作1(商店1進貨):Required,  執行SQL工作2(商店2進貨): Required
   執行畫面:


畫面與情境1, 2, 3是一樣的結果,讓我們再來看看Table實際的狀況
       執行結果:

情境4的結果又回到跟情境1一樣,第一個SQL Task(商店1進貨)的Insert Data是執行成功,但實際上卻是沒有被Commit,而是被Rollback了,看到這裡可能大家會有些困擾,不是三個都是設定Required嗎?不是應該個別是獨立的Transaction交易嗎?為何商店1進貨資料沒有被寫入?原由是時序容器是兩個SQL Task的父容器,此一結果剛好說明MS SQL文件內文的說明,那就是只要父容器(在此是時序容器)有發起一新的Begin Transaction,則子項(在此是兩個SQL Task)會被關聯到父容器的Transaction交易,自已並不會個別發起交易,故執行結果會與情境一的設定一樣,由時序容器決定因為內容Task執行失敗,所以所有的Transaction交易行為都要被Rollback。

以上範例是以時序容器配合交易屬性設定的說明與實驗,給需要用的人參考,未來再使用SSIS建置ETL時,就可以依實際需要加上時序容器的使用了。

參考文章
1.時序容器
https://docs.microsoft.com/zh-tw/sql/integration-services/control-flow/sequence-container?view=sql-server-ver15
2.Integration  Services交易
https://docs.microsoft.com/zh-tw/sql/integration-services/integration-services-transactions?view=sql-server-ver15
3.How  To  UseTransactions in SQL Server Integration Services  SSIS
https://www.mssqltips.com/sqlservertip/1585/how-to-use-transactions-in-sql-server-integration-services-ssis/

陳仲和