This is an SSIS 2017 issue using a Flat File Destination.
We pull the data from an OLD DB Source, run the column through a Sort Transformation and send the result to a Flat File Destination.
In all my testing I'm finding that the order in the resultant text file is correct.
I've used up to one million rows of random data and the sort order is always correct.
My boss, who has used SSIS for many years, says that this is not guaranteed because of the buffering of data in SSIS and we will have to use a scripting method to guarantee the sort order in the test file.
Do any of you know if this is true or not, or perhaps in older versions it was true, but this has been fixed.
The short answer is, your data may or may not be sorted in the order you expect when using a flat file destination component, unless you take specific steps to ensure the ordering of you data.
To make sure your data is sorted in a flat file destination component in the order you expect, the source component must be sorted or a Sort component must be added between the Source and Destination.
If your source is SQL, then add an ORDER BY clause to the query.
After sorting your source data you should set the IsSorted property to True, this is a hint to the downstream components.
The IsSorted property is found by right clicking the source component, select "Show Advanced Editor", select the "Input and Output Properties" tab, select "OLE DB Source Output" (for OLE DB Source) and you will see the IsSorted property in the Common Properties, set it to True.
Of the default sources in SSIS 2017, only the XML Source doesn't have an IsSorted property, for obvious reasons.
I have tested this several times using an OLE DB Source connected to SQL Server with one million GUIDs in a table, with no key(s) or index(es), using an ORDER BY clause, regardless of buffering or the size of the buffer, and it worked correctly every time.