I am creating an SSIS Package which involves pulling data from various resources and inserting into a multiple tables in MS SQL Server. Each time when this package runs, the old Data gets truncated from all tables at start of the package and new values get inserted. The problem here is that When Server is down(from where data is to be pulled) or if any error occurs in the package, the tables are empty since all the values are truncated.
I want to create a transaction point/rollback point in SQL before start of the truncate such that when ever a error occurs or when server is down, I can rollback to Starting point. Any Idea of how to do it?
I Got this Resolved, by creating the snapshot of the database before truncating the values from the table and recover from the snapshot if any error occurs.