在介紹了SQL Server中的事務的概念之后，現在我們可以繼續介紹這次的點滴了。

我們可以把SSIS中的整個package包含在一個事務中，但是如果在package的執行過程中有一個表需要鎖定應該怎么處理呢？SSIS內建的事務處理可以解決這個問題。

SSIS中的包，容器（例如Loop，Foreach Loop，Sequence）或者一個單獨的任務中都可以設置事務處理選項。事務處理選項有下面一些值       　　　　　　　　

1. Required-如有事務則添加，否則新添加一個
2. Supported-如有有事務添加一個，沒有則不添加，這是默認選項+
3. NotSupported-不添加事務處理

內建的事務處理要使用Distributed Transaction Coordinator(MSDTC)服務，這個服務必須開啟。MSDTC允許使用分布式事務處理，例如在一個事務中同時處理SQL Server數據庫和Oracle數據庫。如果沒有開啟這個服務會得到下面的錯誤提示.

Error: 0xC001401A at Transaction: The SSIS Runtime has failed to start the distributed transaction due to error 0x8004D01B "The Transaction Manager is not available.".  
The DTC transaction failed to start. This could occur because the MSDTC Service is not running.  

注意SSIS中包中的元素的事務隔離級別是Serializable，這種級別會影響鎖的持續時間。下面我們來用一個例子說明在如何package中鎖定一個表

1.新建一個SequenceContainer，命名為Test Initialization。

2.這個SequenceContainer主要用來創建測試的環境，創建連個表TranQueue，TranQueueHistory，向***個表中添加一條記錄，這樣模擬一個事物處理過程。我們只是使用這個SequenceContainer來創建測試環境，所以設置它的TransactionOption選項為NotSupported在這個SequenceContainer中依次添加三個Execute SQL，依次他們的設置如下 

/*命名*/                   
Create TranQueue Table       
/*SQLstatement設置*/       
IF NOT EXISTS ( SELECT * FROM sys.objects WHERE object_id =    
   
OBJECT_ID(N'dbo.TranQueue') AND type in (N'U') )   
BEGIN   
execute('CREATE TABLE dbo.TranQueue(message nvarchar(256))')   
END 
/*命名*/  
Populate TranQueue          
/*SQLstatement設置*/  
INSERT INTO dbo.TranQueue VALUES ('Test Message' + CONVERT 
 
(NVARCHAR(23), GETDATE(), 121))  
/*命名*/  
Create TranQueueHistory table      
/*SQLstatement設置*/  
IF NOT EXISTS ( SELECT * FROM sys.objects WHERE object_id =   
 
OBJECT_ID(N'dbo.TranQueueHistory') AND type in (N'U') )  
BEGIN 
execute('CREATE TABLE dbo.TranQueueHistory(message nvarchar(256))')  
END 

3.創建第二個SequenceContainer，命名為Process，TransactionOption屬性設置為Supported，這樣就會添加事務處理。  

4.在這個SequenceContainer中添加一個Execute SQL，命名為ProcessTranQueue，它的SQLStatement設置為下面的語句。這個語句的作用，模擬事務處理，刪除TranQueue表中前10條數據；OUTPUT字句將刪除的數據插入到TranQueueHistory表中，模擬處理結束，更新歷史記錄

DELETE TOP(10) dbo.TranQueue  
OUTPUT DELETED.*  
INTO dbo.TranQueueHistory  
FROM dbo.TranQueue WITH (TABLOCKX)  

5.添加一個Execute SQL，命名為Placeholder for Breakpoint。這個任務不進行任何操作，只是為了在這設置一個斷點然后在這里停下來讓我們有時間驗證是否會鎖定表。
 
6.右擊Control Flow界面添加一個變量v_SimulateFailure，類型為Int32，值為1。

7.添加一個Execute SQL命名為Simulate Failure。用它來模擬錯誤，設置SQLStatement為select 1/0，當pacakage執行到這里的時候會造成錯誤進而回滾。

8.右擊Placeholder for Breakpoint和Simulate Failure之間的連線，點擊Edit，設置Evaluation operation為Expression and Constraint，設置Expression為@[User::v_SimulateFailure] == 1，其他保持默認。這樣之后這個自定義變量的值為1的時候才會繼續往下執行。
 
9.執行package，會得到如圖1的結果，package在斷點處終止。 

  圖1              

10.打開SQL Server Management Studion,選擇對應的數據庫，新建一個Query，執行下面的語句，NOLOCK選項忽略鎖，這個語句查詢得到一條記錄 Message2011-04-10 14:22:31.043，但是這條記錄并沒有提交

SELECT * FROM dbo.TranQueueHistory WITH (NOLOCK)  

11.執行下面的語句

SELECT * FROM dbo.TranQueue  

語句將阻塞在這里，語句一直停留在執行狀態，不會結束。因為在Process TranQueue任務中我們使用TABLOCKX，在這里將等待任務回滾或者提交。或者可以寫成這樣，它任然會阻塞

DELETE TOP(10) dbo.TranQueue  
INSERT INTO dbo.TranQueueHistory VALUES ('Test Message' + CONVERT(NVARCHAR(23), GETDATE(), 121))  

12. 點擊Continue按鈕或者Debuge按鈕，會看到package執行失敗，執行SELECT * FROM dbo.TranQueueHistory WITH (NOLOCK)；因為執行了回滾，不會得到任何結果。SELECT * FROM dbo.TranQueue，任然有一條記錄。

SELECT * FROM dbo.TranQueueHistory WITH (NOLOCK)  

NOLOCK提示忽略鎖，這個語句查詢得到一條記錄 Message2011-04-10 14:22:31.043，但是這條記錄并沒有提交

13.  執行下面的語句，

SELECT * FROM dbo.TranQueue  

sql語句將阻塞在這里，語句一直執行。因為在Process TranQueue任務中我們使用TABLOCKX，在這里將等待任務回滾或者提交。或者可以寫成這樣

DELETE TOP(10) dbo.TranQueue;  
INSERT INTO dbo.TranQueueHistory VALUES ('Test Message' + CONVERT(NVARCHAR(23), GETDATE(), 121))， 

它任然會阻塞     

14.  點擊Continue按鈕或者Debuge按鈕，會看到package執行失敗，執行SELECT * FROM dbo.TranQueueHistory WITH (NOLOCK)；因為執行了回滾，不會得到任何結果。執行SELECT * FROM dbo.TranQueue，任然有一條記錄。

如果設置變量User::v_SimulateFailure的值為0，不會執行Simulate Failure任務，就不會回滾，TranQueue中的記錄會被寫入到TranQueueHistory中。這里有一個很有意思的語句：

DELETE TOP(10) dbo.TranQueue  
OUTPUT DELETED.*  
INTO dbo.TranQueueHistory  
FROM dbo.TranQueue WITH (TABLOCKX)  

如果兩個表的結構有一部分是是一樣的，現在想把一個表的數據導入到另外一個表中，可以使用DELETE SourceTable OUTPUT DELETE.*/DELETE.Column1,DELETE.Column2... INTO DestinationTable FROM SourceTable，這樣***個表中的數據會被“剪切”到第二個表中。

原文鏈接：http://www.cnblogs.com/tylerdonet/archive/2011/04/10/2011806.html

【編輯推薦】

1. SQL點滴之使用attach功能出現錯誤及解決方法
2. SQL點滴之一個簡單的字符串分割函數
3. SQL點滴之重置win7登錄密碼對SQL登錄的影響
4. MySQL數據庫的優化（上）單機MySQL數據庫的優化
5. MySQL數據庫的優化（下）MySQL數據庫的高可用架構方案