1180
Part IX: Business Intelligence
destinations, many transformations provide a way to confi gure error output for rows that
fail the transformation. In addition, many transformations provide both a basic and an
advanced editor to confi gure the component, with normal confi gurations offered by the
basic editor when available.You need to understand features of transforms when choosing them. Some transforms are fully blocking, which
means that SSIS must read the entire data fl ow into memory before it can release any rows, greatly degrading per-
formance. See “Integration Services: Performance Tuning Techniques” at http://technet.microsoft.com/
en-us/library/cc966529.aspx for more information.The standard transformations available in the Data Flow task are shown in Table 52-4.TABLE 52- 4 Data Flow TransformationsTransform Defi nition/Use
Aggregate Functions rather like a GROUP BY query in SQL, generating Min, Max,
Average, and such on the input data fl ow. Due to the nature of this
operation, Aggregate does not pass through the data fl ow but out-
puts only aggregated rows.
Audit Adds execution context columns to the data fl ow, enabling data to be
written with audit information about when it was written and where it
came from. Available columns are ExecutionInstanceGUID,
PackageID, PackageName, VersionID, ExecutionStartTime,
MachineName, UserName, TaskName, and TaskID.
Cache Places selected columns from a data fl ow into a cache for later use by
a Lookup transform. Identify the Cache connection manager and then
map the data fl ow columns into the cache columns as necessary. The
cache is a write once, read many data store: All the data to be
included in the cache must be written by a single Cache transform
but can then be used by many Lookup transforms.
CDC Splitter Splits data from a data fl ow into inserts, updates, or deletes based on
info obtained from a CDC enabled table.Character Map Enables strings in the data fl ow to be transformed by a number of
operations: Byte reversal, Full width, Half width, Hiragana, Katakana,
Linguistic casing, Lowercase, Simplifi ed Chinese, Traditional Chinese,
and Uppercase.
Conditional Split Enables rows of a data fl ow to be split between different outputs
depending on the contents of the row. Confi gure by entering output
names and expressions in the editor. When the transform receives a
row, each expression is evaluated in order, and the fi rst one that eval-
uates to true receives that row of data. When none of the expressions
evaluate to true, the default output (named at the bottom of the edi-
tor) receives the row.
Copy Column Adds a copy of an existing column to the data fl ow.c52.indd 1180c52.indd 1180 7/31/2012 10:29:29 AM7/31/2012 10:29:29 AM
http://www.it-ebooks.info