Non blocking
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
