Precedence Constraints

Each Control Flow task can return an execution state of: Cancelled, Failure or Success. Each task that completes will also return a 'Completed' result. Execution results are used to link tasks together and form the basis of the workflow design. Tasks in the control flow can be disabled, which means they are skipped as the workflow progresses, but subsequent tasks will still execute.

Precedence constraints can be defined to evaluate the combination of both the Task Outcome (Success, Failure or Completed) and the value of an expression using either a logical AND or OR. A task can also be linked to the execution results of multiple tasks, again using a logical AND or OR, via the 'Multiple Constraints' property for the task.

Event Handling

Each Task or Container raises events as it executes. The Event Handler tab in the Package Designer, can be used to add Control Flow tasks in response to events. The 'Executable' drop-down is used to identify the task that the event handler is defined for, and the 'Event Handler' defines the type of event the event handler handles.

Events bubble-up from child tasks to parent containers, with the package being the top-level container. When a child fires an event, the event will be handled at the child-level handler first, then by the next highest container handler, and so on up the container heirarchy. To prevent the event from bubbling-up, set the 'Propagate' property for the event to to False via the Variables window, Event-Handler tab at the selected task level.

Breakpoints

Breakpoints can be set for the package, container or Control Flow task and also in Script Task code. Breakpoints will stop execution of the package at the defined point allowing variables and execution progress to be examined.

Error Rows

Error rows in the Data Flow occur when a row fails an operation. If a transformation would result in data truncation a trunction error occurs. Errors and Truncations can be handled in one of three ways:

Fail

If an error or truncation occurs, then the whole task is assigned a completion status of 'Failure'

Redirect

If an error or truncation occurs, then the whole row is re-directed to a different output path

Ignore

If an error occurs, column values are set to NULL for the attempted transformation. If a truncation occurs, the truncated data is returned for the column.

Redirected rows are typically redirected to a flat file 'error log' destination.

Logging

SSIS provides built-in logging features to capture the exection details for packages and is accessed via 'SSIS > Logging'.

Logging can be enabled for tasks, containers and events, but must first be enabled at the package level.

A number of Log Handler formats are available:

Text File

Used to send data to text files

SQL Profiler

Creates files in a format intended for analysis with SQL Profiler. Files should be created with an '.src' extension

SQL Server

Used to log errors to a table on a SQL Server

Windows Event Log

Logs events into the Windows Event Log

XML File

Creates logfiles in XML format

Use the Details tab of the Logging Configuration dialogue to select which events are logged. Child objects will inherit logging defined for any parent object by default. To set specific logging behaviour for a child object, first clear the logging checkbox for the child object then select it again to set specific logging behaviour for the child.