Transaction in Transform Data Task - DTS
In one of my DTS package I have three Transform Data Tasks. I want to ensure if any of the Transform Data Task fails, all the tasks should be Rollback.
I have enabled the following options in DTS package's Advanced properties:
- Use transaction
- Commit on successful package completion
- Transaction isolation level: Read commited
Even though if any of the above Transform Data Task fails, DTS commits for the remaining successful tasks. As per my knowladge it should not happen as I have enbled above options in DTS to use the transaction.
Or does DTS will takes care in such case if I enable the following options in each of the task's WorkFlow properties?
- Join transaction if present
- Rollback transaction on failure
Can anybody advice me how DTS use Transactions. Why does it get failed in first case?
Thanks in advance,