Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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

http://www.sqlservercentral.com/Forums/Topic998096-391-1.aspx#bm998104

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?

share|improve this question
1  
Did you verify in the table that the rows actually got written? –  Shawn Melton Mar 26 '13 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 –  JuanVelez Mar 26 '13 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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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