ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

Question

Hi,

First of all, retainsameconnection is set to true :).

I have foreach loop , in which all files from specified folder are processed and inserted into SQL tables.

Everything is in loop

1.task : Process data by Script task, and fill variables with INSERT SQL statements

2.task: Execute SQL task BEGIN transaction

3.task: Sequence container which containts all Execute SQL tasks with all necessary INSERT statemens.

4.task: On sequence containter Success COMMIT TRANSACTION

5.task: On sequence containter failure ROLLBACK TRANSACTION

So if I damage input file (in value where is expected integer I put alphabetical character) and one Execute SQL task in Sequence container fails, then ROLLBACK is not possible from reason ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION. In fact I dont understand why, because If I put 10 files into folder and 9 are OK and 1 is not, then COMMIT is executed 9times without problem, only ROLLBACK makes problems.

I used such combination many times (not in loop) and have never problems with COMMIT and ROLLBACK.

Thanks for any idea

Answer 1

Since you already use the Sequence Container.... SSIS has its own implementation for transactions that you should employ:

http://www.mssqltips.com/sqlservertip/1585/how-to-use-transactions-in-sql-server-integration-services-ssis/

Then the question why the ROLLBACK does not work becomes a moot point.

---

Arthur My Blog

Answer 2

Since you already use the Sequence Container.... SSIS has its own implementation for transactions that you should employ:

http://www.mssqltips.com/sqlservertip/1585/how-to-use-transactions-in-sql-server-integration-services-ssis/

Then the question why the ROLLBACK does not work becomes a moot point.

---

Arthur My Blog

I tried SSIS transactions and they kept giving me cannot acquire connections (for database). To fix that you need complicated configs with MSDTC. To avoid all that, I used transactions with TSQL which worked well until now. 

Transactions with TSQL tutorial -

http://microsoft-ssis.blogspot.com/2011/09/ssis-transactions-with-tsql.html

My problem with TSQL transactions -

http://social.msdn.microsoft.com/Forums/sqlserver/en-US/4117978e-ddf7-42d1-a12b-8234458b97aa/the-commit-transaction-request-has-no-corresponding-begin-transaction?forum=sqlintegrationservices