Home > SQL Integration Services > SSIS Checkpoint

SSIS Checkpoint

You can set
package transactions at the entire package level or at any control flow
container level or task level. Windows Distributed Transaction Coordinator
(DTC) services needs to be started on the machine for transactions to work.

SSIS Package
Restartability: To use Package Restartability, in addition to set checkpoint
settings, you also need to set FailPackageOnFailure
to True for each task or container within the package. However, if you want to rerun
any successful tasks that occur before the failure task, you need to use a
sequence container around the group of related tasks that require transactions.

Sometimes, a
series of tasks will need to be rolled back if one of the tasks fails, but you
only want to restart from where it fails. To do this, enable checkpoints in the package level, then
use a Sequence Container that holds
all the associated tasks. The Sequence Container needs to have the transactions
turned on by setting the TransactionOption
to Required at the container level.

To Check DTC: Control Panel à Administrative Tools à
Services Console
à
Distributed Transaction Coordinator Service.

To specify a
task should fail intentionally, set ForceExecutionResult
to Failure. This is very useful in
development stage. 

SSIS event
handlers assigned to an executable scope will propagate down to child events.
E.g. if you assign an OnError even to the package and an error even occurs at
the task, the OnError event handler would file for both the task and the
package and for any containers in between.

Capturing
error information with the OnError event such as ErrorCode, ErrorDescription and SourceName (the task) etc. In
addition, they can used to audit the execution and the event handler Send Mail Task can be used for
notification.

Event handlers
can be turned off for any task or
container by setting the DisableEventHandlers
property of the Task or Container to True. In other words, if you have an even
handler defined, but you specifically do not want it to be invoked for a
specific task, then you can turn off event handlers for that task only.

Because
the commit level is configured on a table-by-table basis, all the data flow and
control flow tasks that operate on a single task need to be grouped together in
a container, and the TransactionOption must be set to
Required” for each container. You
should also implement checkpoints on the containers, which will let you restart
the packages at the point of failure after you have resolved any problems. You
can simplify this implementation by creating a master package that has
checkpoints turned on and that uses the Execute Package Task to call child
packages for each destination table that has transactions enabled.

Advertisements
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: