Mapping with Sorter Transformation

Mapping with Sorter Transformation

# 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.

Lets create a mapping using Sorter Transformation

Source table used for Sorter Transformation, We have 22 records with 2 NULL and 2 Duplicate first_name with upper and lower case.
Mapping with Sorter Transformation. Here the sort key is First Name and sort type is Ascending order.
Output Target Table – All the 22 records ware sorted by ascending first name. The NULL values are treated as High values by default and hence listed in the last row.
Enable ‘Null Treated Low’ property to treat the NULL value as low value. See the result below.
NULL values are listed in the beginning since its low value because of the property we set in the mapping.
Enable ‘Case Sensitive’ property in Sorter Transformation – To consider case while sorting the record. See result below.
The lower case name ‘dup’ is treated as high value and because of ascending first_name sort type, its pushed to last row. What will happen if we change the sort direction to Descending order. See result below.
See the result now – Sorted with First_name Descending order, Lower case listed first and then the other records and NULL in the last.
Enable ‘Distinct’ property in the sorter transformation. This will automatically consider all ports as part of the sort key. Can you imagine what would be the result? Do you think it will remove one duplicate record and load only 21 record out of 22 ? See result below.
We got all 22 records loaded into target, Why? because ‘case sensitive’ property is also enabled. In that case first_name ‘dup’ & ‘DUP’ are not a duplicate record. so no exclusions.
Here we disabled the ‘Case Sensitive’ property and now see the result below.
Only 21 records were loaded. Why because integration service found one duplicate record (‘dup’ & ‘DUP’) and its discarded.

Related Posts: