An interesting question was asked of me from a student in my End to End Business Intellingence class
during the transactions discussion of the SSIS portion. The question is this:

If you have a File System Task in a Sequence container that deletes files from a directory
and a subsequent task in the sequence container fails, will the deleted files be recovered?

Here's the setup: A Sequence Container contains 2 tasks. The first one is the File System Task
that deletes files from a directory and the second one is simply a Script Task that returns a task
result of Failure (to simulate an error so we can trigger a rollback in the transaction). The Sequence
Container TransactionOption property is set to Required and the tasks within the container are set
to Supported.

When the package is run the File Sytem Task will delete a file from a directory and the next task
will produce an error, triggering a rollback.

Since the File System Task completed successfully and deleted the file, but the transaction is
rolled back, will the file be un-deleted.

The answer is NO! Once the file was deleted, even though the task was in a transaction that got
rolled back, the file stayed deleted. The same behavior is exhibited even when your File System Task
Operation is set to Move.

The conclusion of this is to take a different architectural approach with file operations when using
transactions. File operations and transactions just don't mix!