Sorter Transformation Overview
# To sort data in ascending or descending order.
# Can sort data from relational or flat file sources.
# The Sorter transformation contains only input/output ports.
# All data passing through the Sorter transformation is sorted according to a sort key.
# The sort key is one or more ports that you want to use as the sort criteria.
Sorter Transformation Properties
Sorter Cache Size
The Integration Service uses the Sorter Cache Size property to determine the maximum amount of memory it can allocate to perform the sort operation.The Integration Service passes all incoming data into the Sorter transformation before it performs the sort operation. If it cannot allocate enough memory, the Integration Service fails the session. If the amount of incoming data is greater than the amount of Sorter cache size, the Integration Service temporarily stores data in the Sorter transformation work directory. The sorter cache size is set to 16,777,216 bytes by default.
The Case Sensitive property determines whether the Integration Service considers case when sorting data. When you enable the Case Sensitive property, the Integration Service sorts uppercase characters higher than lowercase characters.
You must specify a work directory the Integration Service uses to create temporary files while it sorts data. After the Integration Service sorts the data, it deletes the temporary files. By default, the Integration Service uses the value specified for the $PMTempDir process variable.
Distinct Output Rows
You can configure the Sorter transformation to treat output rows as distinct. If you configure the Sorter transformation for distinct output rows, the Mapping Designer configures all ports as part of the sort key. The Integration Service discards duplicate rows compared during the sort operation.
Null Treated Low
You can configure the way the Sorter transformation treats null values. Enable this property if you want the Integration Service to treat null values as lower than any other value when it performs the sort operation. Disable this option if you want the Integration Service to treat null values as higher than any other value.
The transformation scope specifies how the Integration Service applies the transformation logic to incoming data:
Transaction. Applies the transformation logic to all rows in a transaction. Choose Transaction when a row of data depends on all rows in the same transaction, but does not depend on rows in other transactions.
All Input. Applies the transformation logic on all incoming data. When you choose All Input, the PowerCenter drops incoming transaction boundaries. Choose All Input when a row of data depends on all rows in the source.
Lets create a mapping with Sorter Transformation in the next post.