ssis 2008 transactions

Question

Hi, I am using SSIS 2008 for the loading of data from txt file to sql. In this scenario I want to revert all the data if package failes in any component for that I used transaction: required at package level and transaction: supported at all data flow levels but it is not giving the desired results. Could any one give possible solutions to it.

Thanks prav

Answer 1

Package:
TransactionOption = Required

Containers:
TransactionOption = Supported; FailPackageOnFailure = True; ForceExecutionResult = None

Answer 2

Another option would be

A) IF Upload TextFile to SQL Package Success --> Do Step "X" ("X" is NEXT Step for example)

B) IF Upload TextFile to SQL Package FAILS --> Delete/Truncate Your Table Data --> Do Step "X" ("X" is NEXT Step for example)