Sql-server – SSIS Package Data Flow Task RollBack on Error

sql serverssis

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?

Best Answer

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.

Related Question