Connect

I had an aggregate task in Connect ETL that consistently produced sorted output across hundreds of test runs. I trusted it. Then data size changed, the environment moved, and the order of records in the output changed completely with no error, no warning, and no obvious reason. That was when I started reading what Connect ETL actually guarantees about sort order, and what it deliberately does not.

The /TASKTYPE option defines the type of task in Connect ETL. For sort tasks, no automatic guarantee about equal-key record ordering exists unless explicitly requested. For aggregate tasks, the SORTEDOUTPUT argument controls whether output is sorted when SORTEDOUTPUT is not specified, the output of an aggregate task is not sorted and performance gains are realized. For join tasks, SORTEDOUTPUT can be used in conjunction with /STABLE to ensure that the order of equal-keyed records in the sorted output is the same as in the input. For copy tasks, ALLOWREORDERING specifies that the order of output records is not important and extracted records from the source can be reordered in the absence of ALLOWREORDERING, the order of output records is the same as the order of input records.

The key insight is that Connect ETL optimizes for performance by default. Sorting adds cost. Unless you explicitly declare that order matters in your task type and your sort options Connect ETL is free to produce output in whatever order is most efficient.

By default, the order of records in the output of an aggregate task is not guaranteed to be consistent. Without using the options to request consistency, the output may appear consistent in some cases, but it is not guaranteed. Changes in the task, data size, or environment may result in the order changing. To request a consistent ordering: in the Task Editor, use the Sort aggregated records on the group by fields for target option in the Aggregate dialog, or in a DTL task, use the SORTEDOUTPUT argument to /TASKTYPE AGGREGATE. This guarantees that the output is sorted consistently across the group-by fields, but it may incur a performance cost if it forces a sort where one was not originally required. 

The /STABLE option is the second layer of this. It controls what happens to equal-keyed records specifically not just whether the output is sorted, but whether the original relative order of records with identical key values is preserved.

The /STABLE option is valid for sort, merge, and join tasks. The terms 'stable' and 'non-stable' refer to the order in which records with equal key values leave the task. When a task is stable, the order of the equal-keyed records is completely predictable. For a stable task with a single input file, the original order of equal-keyed records is maintained the order of these records is the same in the output file as in the input file. For a stable task with more than one input file, equal-keyed records from a specific input file appear together in the output file in the same sequence as they did in the input file. When a task is not stable, the order of equal-keyed records is not predictable this is the default. Preserving the original order of equal-keyed records adds a performance overhead. If you do not require a stable task, do not specify /STABLE. 

There is one extra step.

Output order that appears stable in testing can silently change in production when data volume, task parameters, or the execution environment shifts. Connect ETL does not warn you when this happens. The output is still correct by the task's own definition you just did not declare that order mattered.

The pattern that causes the most invisible issues is an aggregate task feeding a downstream join or report where the consumer assumes sorted input but the aggregate task never declared SORTEDOUTPUT. Everything works until the data grows past an internal threshold and the ordering strategy changes.

If you ever see a pipeline that passes all tests but produces inconsistent downstream results in production, do not look at the data first. Look at whether the tasks between source and target declared their sort guarantees explicitly. It is not random. It is Connect ETL doing exactly what it should.

I used to read "the output looks sorted" as "the output is sorted." I now treat observed order as meaningless unless a task explicitly declares it. Adding SORTEDOUTPUT to aggregate tasks and /STABLE to sort tasks where tie-breaking matters is now part of my job design checklist not something I add after a problem appears. The performance cost of /STABLE is real, but it is a known, documented cost. The cost of silent ordering changes in production is not.

Connect ETL defaults to the fastest behavior rather than the most predictable one. Sort order, equal-key ordering, and aggregate output sequencing are all opt-in guarantees declared through SORTEDOUTPUT, /STABLE, PRESORTED, and VERIFYORDER. The options exist. The defaults are intentional. The bugs happen when practitioners assume the defaults are something they are not.