0

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.

user3640552
  • 1
  • 3

3 Answers3

0

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

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

Pawan
  • 1,065
  • 5
  • 10
0

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.
ms118611
  • 1
  • You mean I have to delete the old table when I rename the temporary table. Because If I rename the same name, I will receive an error message something like "the table already exist". – user3640552 Dec 01 '14 at 14:37
  • Yes. The object you are renaming to can't already exist. There are some DBMS specific restrictions on renaming DB objects, so which DBMS you use? To sum up, you drop table once you're sure the temporary table load had finished successfully. If hadn't, you still have the old data. – ms118611 Dec 03 '14 at 21:36
0

You could use transactions with for example TSQL: enter image description here

Joost
  • 1,873
  • 2
  • 17
  • 18