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

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/