CODEWITHSUNDEEP
sql-servertransactionsssisrollback
user399532
user399532

Reputation: 31

SQL-Server Transaction rollback

We have a complex SSIS job and in each transaction we have to inserts around 30K+ records in SQL server. The package is using the local transactions with trans start and rollback statements. I am bit puzzled with this transaction rollback behaviour where it is NOT clearning 2-3 records from one of the table when it rolls back. When I issue a rollback then it should clean all the inserted records in the table but this is NOT happening and instead it is leaving 2-3 records in the table, which is wrong.

Also, the business needs is to either create all the records in one transaction or rollback all the create ones, I cannot do partial commits. I hope this gives a clear picture of what is happening.

Any thoughts?

Upvotes: 0

Views: 635

Answers (1)

user399532
user399532

Reputation: 31

I switched to MSDTC to handle these transactions in SSIS. To me it sounded like a work around to use the begin and commit transactions semantics and I guess the reason for partial rollback. After switching to MSDTC, this issue is resolved.

Many thanks

Upvotes: 1

Related Questions