.DTSX package is there a way to wrap a workflow into a transaction

Question

I have a dtsx package that imports data from a csv into a table, it truncates the table before it loads the data into the table.

The problem is that sometimes for a reason or another the package fails, hence I end with an empty table, I would like to wrap the workflow inside some kind of transaction and restore the table in case of failure.

Is there any way to achieve this?

Answer 1

You can read about SSIS transactions here.

Configure a package to use a single transaction

In this option, the package itself initiates a single transaction. You configure the package to initiate this transaction by setting the TransactionOption property of the package to Required.

Next, you enlist specific tasks and containers in this single transaction. To enlist a task or container in a transaction, you set the TransactionOption property of that task or container to Supported.

1. In SQL Server Data Tools (SSDT), open the Integration Services project that contains the package you want to configure to use a transaction.
2. In Solution Explorer, double-click the package to open it.
3. Click the Control Flow tab.
4. Right-click anywhere in the background of the control flow design surface, and then click Properties.
5. In the Properties window, set the TransactionOption property to Required.
6. On the design surface of the ControlFlow tab, right-click the task or the container that you want to enroll in the transaction, and then click Properties.
7. In the Properties window, set the TransactionOption property to Supported.

   Note
   To enlist a connection in a transaction, enroll the tasks that use the connection in the transaction. For more information, see Integration Services (SSIS) Connections.

8. Repeat steps 6 and 7 for each task and container that you want to enroll in the transaction.

But a better way is usually to load a staging table, and then in an Execute SQL task truncate and load in transaction, eg

begin transaction
truncate table foo
insert into foo (...) select (...) from foo_stg
commit transaction