December 24, 2007 at 2:18 am
Hi Pals,
I have few queries regarding the transaction management in SSIS.
1. I have a SSIS package in which i have a 2 DataFlow Tasks which basically loads 10,000 records in each table.
2. Then i have one Execute SQL Task which contains an 2 UPDATE statements.
The first UPDATE statement update the table1 which has no issues.It is preetty much straight forward.
Inside the 2nd UPDATE statement, i purposefully updating a numeric column with Alphabets. which throws an Exception.
Since I am taking care of Exception Handling in my stored procedure. So my previous UPDATE will get ROLLBACK'ed as expected.
The catch here is, As the update statement is failed(i.e Txn is Rollabcked). Do i need to Rollback the entire data loads i.e
10,000 recs into Table T1 and table T2.
In such scenarios how do we deal such things in Real Time.
Do we Rollback the Entire Process. Can you please suggest me how do we takle such situations in SSIS.
Thanks & Regards.
December 27, 2007 at 3:23 am
I would also like to know how to get around this please.
December 17, 2008 at 8:17 am
You would need to include your tasks in a container such as the For Loop, ForEach Loop or the Sequence Container. These containers have a "Transactions" section in the properties window. By setting the IsolationLevel and TransactionOption you can effectively use a transaction that encompasses not only data flow tasks, but other tasks as well, such as file operations.
In order to use transactions in your SSIS packages, you need to ensure that MSDTC(Microsoft Distributed Transaction Coordinator) is enabled. You can enable this service by going into Control Panel -> Administrative Tools -> Component Services. Drill down to Computers and then right click on My Computer. Click on MSDTC tab and then click the Security Configuration button. Ensure that the following options are selected: "Network DTC Access", "Allow RemoteClient", "Allow Inbound/Outbound", "Enable TIP".
Unless your stored proc is used by another process, you should be able to remove its transaction logic if you use transactions in SSIS. In our company we generally use "Required" for the TransactionOption and "Serializable" for the IsolationLevel. Required just means that the container will support a transaction and Serializable means that no other process can update the files/Database data inside the container while the transaction is active. You can read more about SSIS transactions and the TransactionOption property here:
http://msdn.microsoft.com/en-us/library/ms137690.aspx
More Info about Database Isolation Levels can be found here:
http://msdn.microsoft.com/en-us/library/ms137690.aspx
Hope this helps.
Bob Pinella
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply