We have a complex SSIS job and in one of our task we have to insert 30K+ records in SQL server. The package is using the local transactions with trans start and rollback statements. When the transaction is rolledback it is NOT cleaning all the inserted records in the table and leaving 2-3 records in the table, which is wrong. So in summary when using SSIS with local transactions, it is NOT rolling back the transaction completly. Any thoughts?

how did you implement transactions? you can simply set TransactionOption property of Data flow task as Requiredand FailPackageOnFailure property to Truehttp://www.rad.pasfu.com

What do you mean by local transaction? do you have a Data Flow Task? Tweet me..

I assume that you are using three ExecuteSQL tasks, one to start the transaction, one to commit the transaction and one to rollback the transaction. In the middle is a data flow task where the destination uses the same connection as the three ExecuteSQL tasks. Have you set the RetainSameConnection property on the oledb connection manager to true? Otherwise, SSIS could create a separate connection on each of the tasks.Russel Loski, MCT