Data Warehouse Patterns - SQL Server Integration Services Design Patterns - page 330

does not match one of the lookup components, the package will still fail; however, you
may commit rows preceding the errored row that have already been sent to the destina-
tion to that table before the failure and can cause all the data to be in an inconsistent
state.
There are several ways to accomplish this rollback behavior:
SSIS transactions: Integration Services natively has the ability to
wrap tasks and containers into transactions and, in theory, will reverse
any durable changes to the relational database in the event of an error
in the scope of that transaction. In practice, however, I've found that
using the SSIS transaction functionality can be challenging even on a
good day. Leveraging transactions directly in SSIS requires a lot of
things to align at once: all involved systems must support DTC trans-
actions, the DTC service must be running and accessible at each of
those endpoints, and all relevant tasks and connections in SSIS must
support transactions.
Explicit SQL transactions: This method involves manually creating
a transaction in SSIS by executing the T-SQL command to engage a
relational transaction on the database engine. Using this method, you
essentially create your own transaction container by explicitly declar-
ing the initialization and COMMIT point (or ROLLBACK , in the event
of an error) using the Execute SQL task. On the database connection,
you'll need to set the RetainSameConnection property to True
to ensure that all operations reuse the same connection and, by exten-
sion, the same transaction. Although this method does require some
additional work, it's the more straightforward and reliable of the two
transactional methods of strategic rollback.
Explicit cleanup: This design pattern involves creating your own
environment-specific cleanup routines to reverse the changes due to a
failed partial load, and it typically does not engage database transac-
tions for rollback purposes. This method requires the most effort in
terms of development and maintenance, but it also allows you the
greatest amount of flexibility if you need to selectively undo changes
made during a failed package execution.
Unhandled Errors