TRANSACTIONS in SSIS (error: The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

source : http://social.msdn.microsoft.com/forums/en-US/sqlintegrationservices/thread/c2c1ff73-f802-44ae-b60e-7992dca18dbf

Q: I'm receiving the below error when trying to implement Execute SQL Task.

"The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION." This error also happens on COMMIT as well and there is a preceding Execute SQL Task with BEGIN TRANSACTION tranname WITH MARK 'tran'

I know I can change the transaction option property from "supported" to "required" however I want to mark the transaction. I was copying the way Import/Export Wizard does it however I'm unable to figure out why it works and why mine doesn't work.

Anyone know of the reason?

A:

If you are having three Execute SQL tasks like:
BEGIN TRAN
 |
 |
 |
V

 |
 |
 |
V
END TRAN

Then, you have to set, "RetainSameConnection" property on the connection manager to true. Otherwise, you will get an error message like the one you have reported.