TransactionOption Property Not Working - Feedback Needed

I have a package which consists of 3 Execute SQL Tasks -

1) Drop old database & Restore a new one
2) Run DDL
3) Run DML

My task was to put all of these in a transaction. I started using the "TransactionOption" property for this package to be "Required" and each child tasks also were set to the same transaction option property of "Required". However, after running the package, it errored out saying Task 1 cannot be in a transaction (which is logical) so, I removed the transaction required property from the first task and kept it on for the remaining 2 tasks. In addition, I have also set up a failover strategy, where in if these tasks were to fail, the package should restore the previous working copy of the backup. To test this scenario, I deliberately created an error in the DML task, so logically only this task should rollback, instead it rolls back the entire transaction and to my horror the failover step is not executed as well.

Is there something which I am not doing correctly

How do I go about this

Thanks,

Deepak.