I created multiple SSIS packages and one parent package that execute them.

Each package designed to delete the existing records and reload data from different source tables based on the cutoff date. In case of the parent package, it just listed the child packages based on the sequence.

When TransactionOption for all packages, both parent and child, were set to 'Supported' it works as designed.

After changing TransactionOption of all child package to 'Required', entire SSIS package hung and was never completed; TransactionOption of parent package was set as Supported. When each child package was executed without calling from parent, each works as deisnged even though its TransactionOption is 'Required'. However, when they are executed from the Parent pacakge, none of child package was executing but just hung.

Thank you in advance for your ad

There is issue in SSIS around this , not sure about 2012

http://social.msdn.microsoft.com/Forums/sqlserver/en-US/22aada9c-5244-463a-aca6-a84fd61e6370/parentchild-packages-and-transaction-option-not-working-as-expected

Hi Alan,

When TransactionOption for all packages, both parent and child, were set to 'Supported' it works as designed.

if the packages are working in the order they are designed, my questions is then why you are setting transactions in SSIS package?

If the child packages are performing their individual and independent tasks, and they are called sequentially(serially) in parent package, doesn't that solve your purpose?

When TransactionOption is set to 'Supported', SSIS will not use Transaction/RollBack if there is no task having TransactionOption = required.

A reason why I tried to enable transaction/Rollback is the same reason why a developer adds transaction to stored procedure even though the stored procedure works as designed without transaction.

I was using 2012.

It seems weird. In my case there was no error but hung at the validation step.

Alan,

This blog post on SSIS transactions is useful:

http://microsoft-ssis.blogspot.com/2011/09/ssis-transactions-with-tsql.html