Hi, I would apprecaite any suggestion on how to implement transaction without using DTC. I tried following approach: Approach 1 1) I created a package -> sequence contaier within sequence container i added 3 Execute SQL task Task 1 aving sql statement BEGIN TRAN, Task 2 having execute stored procedure and returns value and I store the return value in Variables having package scope Task 3 I use expression to check against returned value and based on value I execute Commit or Rollback statement In my Stored procedure I do all my logic to do data transfer from source to destination and updating several other tables In my stored procedure I check @@Error after every operations and in case of error I return value from my procedure to indicate a rollback. Approach 2 I created a package -> sequence contaier within sequence container i added 2 Execute SQL task and 2 Data flow task Task 1 aving sql statement BEGIN TRAN, Data Flow Task 1 Here i use oledb source and oledb destination to copy data from source to Destination Data Flow Task 2 Here i use oledb source and oledb destination to copy data from source to another table I have also configured OnError Event handler on Data FLow Task 1 and Data Flow task 2. In OnError event handler I have a script task that Writes to a variable a value to sigify success or failure Task 2 I use expression to check against value stored in Variable and based on value I execute Commit or Rollback statement When I run this then If an error occoured at say Data Flow Task 2 and Data Flow task 1 is successful. It's not rolling back the transaction. It fails at Data flow Task 2 and does not execute last task wihch has rollback. Any suggestion or pointer would be helpful. Also wich approach should I use Approach 1 or 2 or is there any other way? Thanks MS

Hi, According to me, approach 1 looks better; however, a change in this approach would be good. The change would be to have the transaction (BEGIN TRAN, COMMIT TRAN, ROLLBACK TRAN) within your SQL stored procedure instead of having 2 extra SQL tasks in your sequence (one for BEGIN TRAN and the other for COMMIT or ROLLBACK). So, your procedure's structure would be something like this - BEGIN TRY BEGIN TRAN <your business logic here> COMMIT TRAN END TRY BEGIN CATCH ROLLBACK TRAN END CATCH This would work even if the MSDTC service is not running (since this service is for the SSIS packages). Regarding your approach 2, In case your DFT 1 fails, no data would be loaded into your Table1. And your DFT 2 wouldn't execute since DFT 1 has failed (I am assuming you have a 'On Success' constraint between DFT 1 and DFT 2); so no data would be loaded into your Table2 as well. In case your DFT 2 fails, no data would be loaded into Table 2; however, there is data in Table 1 since DFT 1 succeeded. What you can do here is - in the OnError event handler of your DFT 2, you can have an Execute SQL Task that would unload any data that has been loaded in your Table1 by the DFT 1. So there is no need to have the other 2 Execute SQL Tasks in the approach 2 as well. Please let us know in case you need any further information. Cheers, ShalinShalin P. Kapadia

Approach 2 is better because you use native SSIS components, for example [Data Flow] component to transfer data between source and destination. Those components support transactional behaviour, for example "TransactionOption" porperty where you could specify whether a component supports parent transaction or a nested transaction is required. OnError you need to output rows into other error table for further examination. The above-mentioned requires MSDTC. Approach 1 does not require an SSIS package as stored procedure could be called from a job using SQL Server agent.Sergei

Regardless of which approach you're using, the key here is to set a property on your connection manager. If you don't, either method could "fail" to participate in the transaction. You need to set the "RetainSameConnection" property to TRUE. If you leave it at the default "false", then the managed connection will start a connection pool (several connections) and each of your package's parts will (likely) use a different connection. If they do, then they won't participate on the transaction that was started on a specific connection. Talk to me now on

Hi Shalin, Thanks for the suggestion. I'll implement apporach one incorporating your suggestion. I also have a trigger on that table which writes to another audit table. If an exception happens will it roll back all the changes in target table and audit table? I tested it with few records it roll backs all the changes. In real environment we may have severl hundred thousand records. Will there be any issues when dealing with large volume of data. Thanks MS

Hi MS, Yes, you are right. In case the procedure throws an error, all the changes including the changes to the Audit table would be rolled back. If you want the records in the Audit table to be preserved, you could go through the Autonomous Transactions feature of SQL Server 2008 and use it in your project (the feature is available in SQL Server 2008 only; I am not sure if you are using SQL Server 2008 for your project). In case the data is huge, the only impact it would have is the procedure would take a little more time to execute since it has a transaction. Nevertheless, if your scenario is such that you have to have a transaction, irrespective of the amount of data you would be dealing with, you must include a transaction. Let us know in case you need any further information. Cheers, ShalinShalin Kapadia

Hi Shalin, Thanks for the response. I am using SQL merge command in 2008 to get net new between table A and Table B for any rows inserted ,updated and deleted. I use OUTPUT clause of merge command to load the changes (between Table A and Table B) in a temp table. Then I use merge command again between Table C and temp table. I need to know if there will be any concern or issues that I should be aware of when using OUTPUT clause in merge command to load data in temp table for further merge with another table. Thanks again for the response. Thanks MS