Hello everyone,
I have one SSIS package which moves data from one SQL Server to another SQL Server by apply certain transformations on source data.
My requirement is, If anyone task fails, I need to rollback all the previous DML operations that have been performed in the destination SQL Server.
To implement this, I've taken the reference of below link:
1. https://social.msdn.microsoft.com/Forums/en-US/7e507454-0f3a-4357-afc5-92b50bcd4c13/rollback-in-ssis?forum=sqlintegrationservices
2. https://stackoverflow.com/questions/13626755/how-to-transaction-rollback-in-ssis
To implement ROLLBACK, I have set the transaction option to 'required' for the sequence container (which contains all the tasks which requires to run in transactions) and 'supported' for the the components that I want to rollback in case of failure.
But the issue is when I start execution of package, Visual studio stopped responding. I do not have any other option except kill the visual studio from Task Manager at this stage. Next time when I open my SSIS Solution visual studio again stop responding and shows "Validating sequence container" message in status bar.
Please help me out with this.
Any help is highly appreciated.
Thank you.