You certainly could use temp tables (though if you take that approach, I recommend using permanent staging tables to avoid potential issues with temp tables). However, if this all-or-nothing operation is critical, you must also account for the possibility that the operation loading from the temp (or staging) tables to the destination tables load could also fail.

To revert back to the previous copy of the data in the event of any error is a little more difficult. Unless you use SSIS transactions (which I don't recommend, by the way), you'll need to build in a mechanism for each table to store the current contents of that table for each operation. This would probably look like another set of staging tables, this time used just to persist the last known good set of data. In the event of any error, you'd essentially reverse that ETL process to load from that second set of staging tables back to the destination tables.

hth,

Tim