Hello Everyone, I am currently working on SSIS packages to load my Staging Database and Datamart.However I am stuck with one major problem "RollBack". Incase of any issues while inserting records in to datamart , I want to rollback the entire data that has been inserted .How do I achieve this? Any other general rules or considerations while inserting data into Datamart is most welcome. Thanks in Advance, Kannan
As @Usman Butt wrote, the **`TransactionOption`** needs to be set to **`Required`**. But it can be set even for a single data flow and do not needs to be set on a Sequence Container. Sequence container simplifies this if you want to set it for multiple control flow tasks (including data flow) as the default TransactionOption` is **`Supported`**, what means if the parent has `TransactionOption` set to `Required`, then all child tasks with Supported option will execute under the same transaction. Optionally you can set the TransactionOption on the Package Level and then the whole package will executin inside single transaction and whatever will fail, all the work will be rolled back. In case you need something to out of transaction control (eg. some kind of logging) then for such Control Flow items you have to set the `TransactionOption` to **`Not Supported`**. In this case such control will be executed outside the transaction scope. Be carefull when setting the `TransactionOption` to `Required` as the default **`Isolation Level`** is set to **`Serializable`**. If not used carefully you can end up with unwanted **lockingk** and performance degradation. The use of transactions needs to be analysed and designed appropriatelly especially in a case where multiple packages can operate upon single table.
SSIS do support transaction feature. By default every task of the package executes in its own transaction. What you need to do is put all your related tasks in a Sequence Container and set the TransactionOption property appropriately. (I guess it should be set to "Required"). This way you can ensure either all of the related tasks complete successfully or if any of them fails, the transaction will be roll-backed. HTH.
Hello, Let us say the sql job having 3 steps and each step executing a different ssis package.When the third step fails, How the SQl JOB rollback/delete the step1,step2 ssis packages inserts? Thanks for your reply in advance