Faking success in SSIS

A SQL Server Integration Services package has a set of properties with similar names that let you:

  • force the result of running the package
  • return a custom value from the package

Their similar names make it easy to confuse these properties. To make matters worse, their descriptions were mixed up in at least one place in Books Online (now fixed). I've just checked BOL one more time, so let's clear up the confusion before I forget which property is which.

These properties apply not only to the package itself, but also to other objects in a package that inherit the DtsContainer interface, including both containers and the TaskHost that wraps tasks.

You can fake Success or Failure

You can force the ExecutionResult of a task, container, or package by selecting a value for the ForceExecutionResult property. The available values are the members of the DTSForcedExecResult enumeration:

  • None, -1, the default
  • Success, 0
  • Failure, 1
  • Completion, 2

Ironically, it's probably more useful to fake failure than success. As BOL explains: "You can use the ForceExecutionResult property on a task or container to test the use of checkpoints in a package. By setting ForceExecutionResult of the task or container to Failure, you can imitate real-time failure. When you rerun the package, failed tasks and containers will be rerun."

You can return a custom value or user-defined data

You can also return some user-defined data from a task, container, or package:

  1. Set the Boolean ForceExecutionValue property (no "d") to True to turn on this feature. The default value is False.
  2. In the designer, select the ForcedExecutionValueType of the user-defined data that you intend to provide. The default value is Int32. (This property exists only in the designer; you won't find it in the API.)
  3. Finally, provide a value for ForcedExecutionValue. The default value is 0 (zero). Get creative with the SSIS expression language and return custom status information from your running tasks. Then you can compare or check these values in precedence constraints to fine-tune your control flow.

Have you seen the new SSIS training book?

Brian Knight's latest book, Knight's 24-Hour Trainer: Microsoft SQL Server 2008 Integration Services, recently released by Wrox, takes an innovative approach with 48 modular lessons and accompanying videos on DVD.