Informatica sources and sorted ports

Informatica supports sorting in the Source Qualifier in addition to a sorter transformation. This came up with I was working on updates to an existing mapping that outputs data to a CSV file. The output file is sorted by date and the sort is controlled in the SQ (not a sorter transformation). Sorting in the SQ will generally be more performant because the sort is done at the database level.

It’s very simple to sort the source data since Informatica adds the sorted SQ ports to the generated SQL statement. The only caveat is that you have to put your sorted ports at the top of the list of ports in the SQ as mentioned in the Informatica help.

From the Informatica Help:

Source Qualifier->Properties->Number Of Sorted Ports
Indicates the number of columns used when sorting rows queried from relational sources. If you select this option, the Integration Service adds an ORDER BY to the default query when it reads source rows. The ORDER BY includes the number of ports specified, starting from the top of the transformation. When selected, the database sort order must match the session sort order.

If you select 4 as your number of sorted ports, Informatica is going to generate an order by clause that uses the first 4 ports in your SQ transformation.