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 rollb开发者_运维百科ack all the create ones, I cannot do partial commits. I hope this gives a clear picture of what is happening.

Any thoughts?

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