Classifying Data
Flow Transformations on performance considerations:
The three classifications of Data
Flow transformations that are based mainly on
performance considerations.
Nonblocking
Synchronous Row-Based Transformations:
These transformations work on a
row-by-row basis and modify the data by changing
the data in columns, adding new
columns, or removing columns from the data rows,
but these components do not add
new rows to the data flow. The rows that arrive at the
input are those that leave at the
output of these transformations. These transformations
have synchronous outputs and make
data rows available to the downstream components
straightaway. In fact, these
transformations do not cause data to move from one buffer
to another; instead, they
traverse over the data buffer and make the changes to the data
columns. So these transformations
are efficient and lightweight from the process point
of
view. The transformations that readily pass the data to the downstream
components are classified as Row Transformations.
The
following Row Transformations are examples of Nonblocking synchronous row-based
transformations:
Ø
Audit
transformation
Ø
Character
Map transformation
Ø
Conditional
Split transformation
Ø
Copy
Column transformation
Ø
Data
Conversion transformation
Ø
Derived
Column transformation
Ø
Export
Column transformation
Ø
Import
Column transformation
Ø
Lookup
transformation
Ø
Multicast
transformation
Ø
OLE
DB Command transformation
Ø
Percentage
Sampling transformation
Ø
Row
Count transformation
Ø
Row
Sampling transformation
Ø
Script
Component transformation configured as Nonblocking Synchronous
Row–based
transformation
Ø
Slowly
Changing Dimension transformation
Partially
Blocking Asynchronous Row-Set-Based Transformations:
Integration Services provides
some transformations that essentially add new rows in the
data flow and hold on to the data
buffers before they can perform the transformation.
The nature of such transformations
is defined as Partially Blocking because these
transformations hold on to data
for a while before they start releasing buffers. As
these transformations add new
rows in the data flow, they are asynchronous in
nature. For example, a Merge
transformation combines two sorted data sets that may
be the same data, but it
essentially adds new rows in the data flow. The time taken
by this transformation before
starting to release the buffers depends on when these
components receive matching data
from both inputs.
Following is a list of Partially
Blocking asynchronous row set-based transformations:
Ø
Data
Mining Query transformation
Ø
Merge
Join transformation
Ø
Merge
transformation
Ø
Pivot
transformation
Ø
Term
Lookup transformation
Ø
UnPivot
transformation
Ø
Union
All transformation
Ø
Script
Component transformation configured as Partially Blocking Asynchronous
Row-set-based
transformation
Blocking
Asynchronous Full Row-Set-Based Transformations:
These transformations require all
the rows to be assembled before they can perform
their operation. These
transformations can also change the number of rows in the
data flow and have asynchronous
outputs. For example, the Aggregate transformation
needs to see each and every row
to perform aggregations such as summation or
finding an average. Similarly,
the Sort transformation needs to see all the rows to
sort them in proper order. This
requirement of collecting all rows before performing
a transformation operation puts a
heavy load on both processor and memory of the
server. You can understand from
the nature of the function they perform that these
transformations block data until
they have seen all the rows and do not pass the data to
the downstream component until
they have finished their operation.
The following are Blocking
asynchronous full row-set-based transformations:
Ø
Aggregate
transformation
Ø
Fuzzy
Grouping transformation
Ø
Fuzzy
Lookup transformation
Ø
Sort
transformation
Ø Term
Extraction transformation
Resources:
For more detailed information regarding this study, please see
Ø
Microsoft SQL Server 2008 Integration Services
by Ashwani Nanda
Ø
Microsoft Books Online
