I tried running a package and it failed but I noticed in the output box (in visual studio) that it managed to write 2000+ rows to a table. I was wondering if an SSIS package would roll back the transactions in my Data Flow Task if the overall package failed. I found this thread


which states that in order for the package to rollback DFT changes:

Set the TransactionOption property of the package to "Required" Set the TransactionOption property of the dft to "Supported"

I had both the Package and DFT Transaction Option property set to supported so I am guessing that the rows written to the table were not rolled back... Do you all agree that this is the best way to make sure table changes are rolled back in the event the package fails? Will this also rollback file System Tasks such as file moves on the local file system?

  • 1
    Did you verify in the table that the rows actually got written?
    – user507
    Mar 26, 2013 at 18:15
  • Yes. They did get written. I truncated the table and reran the package. It stopped on error and when I checked the table, the rows had been written again. I have not tried doing what the URL in my question states yet. For all I know that might do the trick (of rolling back changes) But i wanted to check here first if someone else knew if that would work from their past experience or if they had an alternative, better option
    – Juan Velez
    Mar 26, 2013 at 19:29

1 Answer 1


I would design each package as a self-contained unit that can be rerun without any manual preparation or clean-up. Relying on SQL transactions can dramatically slow execution and as you noted does not cover all the non-SQL Tasks.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.