Chapter 5: Debugging & Error Handling with SSIS Packages.

Create control flow by using the control flow designer.

• Can co-ordinate txns with package restart - can be configured to restart from pt of failure when pckg rerun.

Configure txn handling for packages, containers, tasks.

• DTC - start svc + tasks that are to be part of txn must work natively with DTC svc.
• TransactionOption - Required.
• Txns work at control fow lvl, NOT data flow.

Connecting Control Flow Objs with Precedence

• Precedence constraints - control flow connectors bet. tasks & containers. NOT in data flow. Connectors there are 'data paths'.
• solid lines - && precedence -
• dashed lines - precedence - task will run when either of the precedence conditions is met.
• functional symbols - an SSIS expression has been embedded in the constraint for evaluation.

Set checkpoints to define restart pts

• restart @ point of failure.
• Enable pckg to use chkpts - PackageProperty.SaveCheckpoints -> TRUE, PackageProperty.CheckpointFileName - supply, PackageProperty.CheckpointUsage - IfExists - pckg runs fom beginning if file not present, or run from identified pt where exists. (If CheckpointUsage = Always, file must be present or pckg will not start).
• Set checkpoints at various stages of the pckg -
• 
  

Create Event Handlers

• -> Event Handlers tab in pckg designer, select executable, select event handler event.
• event handlers assigned to a particular scope will propogate downwards to child events when the event fires.
• Capturing err. info. with OnError event - capture sys. variables e.g. ErrorCode, ErrorDescription, SourceName (i.e. the task).
• Event handlers can be turned off for any task/container - DisbaleEventHandlers -> TRUE.

Implement error handling

Handle errors by configuring data flow paths

• Data paths - output rows that have successfully gone thro a component, as well as errored rows where error output is set to ignore failure for said task.
• Using error paths to route failed rows - red connectors. Contain data rows that fail in a component, when the error rows are set to be redirected. To use, configure the task's error output.
• not all components use error paths e.g. Multicast.
• error output - 3 options: Ignore Failure, Fail Transform, Redirect Row.
• error handling options are available for entire row + per col.

Handle errors by configuring control flow paths

Debug Packages

Debug progress reporting

• Even after an error, other components in the pckg might still be executing.
• Allowing mult. errs - MaximumErrorCount property -

View intermediate results by using debug windows

Examine the package state by setting breakpoints

• Control flow debugging - set bpts, which will pause the control flow execution so that you can observe the execution state.
• bkpts - only in control flow.
•