Issue in setting TransactionOption property in SSIS Package

Question

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.

Answer 1

Hi @Kesha Shah ,

Normally, it would be validating all what is inside fo the sequence container; including the connections used; database object definitions; etc. This may be affected if you are working off-line of if SSIS cannot get connection to those sources/objects.

As someone mentioned in the link you provided, he cannot connect to database after putting tasks in sequence container.

You may refer to see if it is helpful.

https://stackoverflow.com/questions/19869276/cannot-connect-to-database-after-putting-tasks-in-sequence-container

Regards，

Zoe

---

If the answer is helpful, please click "Accept Answer" and upvote it.

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.
Hot issues October