Datasheet

Chapter 1: Welcome to SQL Server Integration Services
17
Row Sampling: Captures a sampling of the data from the Data Flow by using a row count of the
total rows in the Data Flow.
Script Component: Uses a script to transform the data. For example, you can use this to apply
specialized business logic to your Data Flow.
Slowly Changing Dimension: Coordinates the conditional insert or update of data in a slowly
changing dimension.
Sort: Sorts the data in the Data Flow by a given column.
Term Extraction: Looks up a noun or adjective in text data.
Term Lookup: Looks up terms extracted from text and references the value from a reference
table.
Union All: Merges multiple data sets into a single data set.
Unpivot: Unpivots the data from a non - normalized format to a relational format.
Error Handling and Logging
In SSIS, there are several places that you can control error handling, and they depend on whether you
are handling task or Data Flow errors. For task errors, package events are exposed in the user interface,
with each event having the possibility of its own event handler design surface. This design surface is yet
another area where you can define workflow along with the task and Data Flow surfaces you ’ ve already
learned about. The event - handler design surface in SSIS is where you can specify a series of tasks to be
performed if a given event happens for a task in the task flow. There are event handlers to help you
develop packages that can self - fix problems. For example, the
OnError error handler triggers an event
whenever an error occurs anywhere in scope. The scope can be the entire package or an individual
container. Event handlers are represented as a workflow, much like any other workflow in SSIS. An ideal
use for event handlers would be to notify an operator if any component fails inside the package. You
learn much more about event handlers in Chapter 17. You can also use the precedence constraints
directly on the task flow design surface to direct workflow when a proceeding task fails to complete or
evaluates to an expression that forces the workflow change.
An ultimate error within a Data Flow Task can be captured and handled with an error handler, but for
finer control within the data pipe itself, each transformation must be configured to define the action that
should be taken if a specific error occurs while processing the data. You can define whether the entire data
transformation should fail and exit upon an error, or that only the bad rows should be redirected to a
failed Data Flow branch. You can also choose to ignore any errors. The error handler shown in Figure 1 - 8
defines that if an error occurs during the Derived Column Transformation, the error rows will be output
to a new error data stream. You can then use that outputted information to write to an output log or a
destination connection as seen in Figure 1 - 8.
❑
❑
❑
❑
❑
❑
❑
❑
c01.indd 17c01.indd 17 8/28/08 12:01:21 PM8/28/08 12:01:21 PM