SSIS - not truncate if it fails

Question

Currently I have an Excel file that I load into a table every night. To do this, I make a TRUNCATE of the table and then I have a data flow in which I load the data from the Excel file to my table.

In the case the loading fails, I would like not TRUNCATE. Is it correct to make a rollback?

thank you.

Answer 1

You should see how transactions are handled in SSIS. For more details please read below

http://msdn.microsoft.com/en-us/library/cc304421.aspx

Answer 2

TRUNCATE is an operation you can't simply rollback. I suggest you other method:

1. Load data to temporary table.
2. If everything was fine rename temporary table to your table name and voila.
3. In case there was a failure you'll still have an image of your original table.

Answer 3

You could use transactions with for example TSQL: