SSIS可靠性和擴展性—事務

在這一個隨筆中將介紹在package中如何使用事務來保證數據的完整性和一致性。在SSIS中有兩種事務：
 　　分布式事務處理事務（Distributed Transaction Coordinator Transactions）：在task，package中使用單獨一個或者多個事務處理
 　　本地事務（Native Transaction）：SQL Server引擎級別的事務，在一個連接中使用T-SQL中的事務

注意：在SQL Server聯機叢書中MSDTC是這樣定義的：Microsoft 分布式事務處理協調器 (MS DTC) 允許應用程序跨兩個或多個 SQL Server 實例擴展事務。此外，該協調器還允許應用程序參與由符合 Open Group DTP XA 標準的事務管理器管理的事務。

在這里我們將用4個小的練習來學習SSIS中的事務，他們分別是：
 　　單個的package：使用DTC建立一個事務
　　 單個的package：使用DTC擴展多個事務
 　　2個package：使用DTC建立一個事務

單個package：使用T-SQL中的事務，就是本地事務
在package中使用該事務需要設置package或者task的屬性。如果動手做這些練習，會看到這些屬性更加精確的解釋，如下圖1

圖1

下面說明TransactionOption的屬性值的具體含義：

Supported ：支持的，如果在父任務中存在事務，該任務也將被添加到事務中來
Not  Supported： 不支持，如果在父任務中發起事務，該任務不會被添加到事務中
Required： 需要的，如果父任務中沒有發起事務，該任務將會發起一個事物，如果父任務發起事務，則將自己添加到這個事務中去

單個package，單個事務

創建一個package，命名為SinglePackageSingleTransaction，在Control Flow中拖放3個Execute SQL Task，命名如圖2：

圖2

在數據庫中我們可以看到只插入了一條數據，這沒有疑問，如果想讓這個表T1在下面的task出現錯誤的情況下不創建該如何設置呢？首先要告訴Package在開始之行的時候開啟一個事務，點擊空白處按下F4鍵設置package的TransactionOption屬性，如圖3:

圖3

然后要告訴package中的task，為了防患未然，需要融入到這個事務中來，依次點擊選中這些package按下F4鍵設置它們的TransactionOption屬性，如圖4

圖4

注意：這里需要說明的是，它們默認的選項值就是Supporated，根本不需要我們動手設置，這里啰嗦地說明，只是為了更好的理解它的工作原理。
現在重新執行package，一個DTC事務將會開啟，所有的task將被監視，因為最后一個task出現錯誤，整個package將會被回滾，T1表也不會被創建。可以通過查看Visual Studio的輸出（View->Output）來查看信息：

SSIS package "OnePackageOneTracsaction.dtsx" starting.

Information: 0x4001100A at OnePackageOneTracsaction: Starting distributed transaction for this container.

Error: 0xC002F210 at Insert into value ERROR, Execute SQL Task: Executing the query "insert dbo.T1 values('A')" failed with the following error: "Conversion failed when converting the varchar value 'A' to data type int.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

Task failed: Insert into value ERROR

Information: 0x4001100C at Insert into value ERROR: Aborting the current distributed transaction.

Warning: 0x80019002 at OnePackageOneTracsaction: SSIS Warning Code DTS_W_MAXIMUMERRORCOUNTREACHED.  The Execution method succeeded, but the number of errors raised (1) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.

Information: 0x4001100C at OnePackageOneTracsaction: Aborting the current distributed transaction.

SSIS package "OnePackageOneTracsaction.dtsx" finished: Failure.

單個package，多個事務 

 第2個聯系我們來在單個package中同時開啟兩個事務。如圖5，創建一個package命名為SinglePackageMultipleTransactions.dtsx。

圖5

這個package中有兩個SequenceContainer，每個都有自己的task。在Start Tran1內開啟事務，它的子任務將會添加到事務中。Start Tran2開啟另外一個事務，它的子任務會被添加到事務中，但它的子任務故意出錯。每個task里面的SQL語句和第1個例子一樣。這里我們想開啟事務之后，讓其可以建立表，插入一條正確的數據。當我們有一些相互分離的子任務，將他們分類放在不同的SequenceContainer中，設置各自的事務，讓它們互不干擾，這種方法會很有用。下面列出它們的TransactionOption設置。

設置好這些值之后執行package，結果如圖6，第一個Container成功了，第二個失敗了。

圖6

在數據庫中我們看到，表T1被創建，一行數據被插入到表中。

2個package，1個事務

這個練習中我們將新建2個package，分別命名為Caller.dtsx和Called.dtsx。這里我們想使用一個事務包含這2個package。讓Caller創建表，然后使用ExecutePackage調用另外一個package，Called，這個package自己也來創建一個表并插入數據。然后故意在Caller這個package中添加一個招致錯誤的task。我們想讓這2個package都回滾，如圖6是建好的Caller，圖7顯示Called。

圖6

圖7

下面是設置它們的TransactionOption值和SQL語句

執行這個Caller，到第3個task的時候遇到錯誤，兩個package都會回滾，看上去像是在執行一個package一樣。因為Caller開啟一個事務，Called也設置了Supported屬性，它也被包含在這個事務中。
如果Caller的TransactionOption設置為NotSupported，它會創建自己的表Caller并插入數據。
　　

 單個package，本地Sql Server事務

這個練習和上面的3個都不一樣，它不再使用MSDTC來管理事務，而是使用SQL Server中的事務處理。這里演示怎樣使用SQL Server自帶的事務來從錯誤中回滾，但是這里的事務只能使用SQL Server數據庫，如果連接其他數據庫就不能工作了。創建一個package，命名為SinglePackageUsingNativeTracsaction.dtsx，如圖8創建拖放3個task。下面給出這3個task的SQLStatement屬性

要使三個task使用同一個transaction必須保證他們使用同一個數據連接，設置它們所使用的連接的RetainSameConnection為true，如圖8

圖8

要使三個task使用同一個transaction必須保證他們使用同一個數據連接，設置它們所使用的連接的RetainSameConnection為true，如圖9

圖9

從圖中我們可以看到，SQL Server新建了事務，最后提交了該事務。