none
Sort transformation drops rows?

    Question

  • While debugging an SSIS package (SQL 2017 target, VS 2015 with SSDT), I noticed that one of the Sort transformations apparently "lost" about 85% of all the records - and the "Remove rows with duplicate sort values" is Unchecked.  Out of 471,226 rows in to the Sort transformation, only 66,899 are reported to be passed to the next transformation (a Merge Join). 

    I think I saw that this (Sort -> Merge) may have been an issue in 2008, but I don't recall seeing anything more recent.

    What gives?

    (My VS shows the following:)

    BI Developer Extensions v2.1.1 for Visual Studio 2015 - An add-in to extend SQL Server Data Tools

    Microsoft SQL Server Data Tools 14.0.61712.050

    Microsoft SQL Server Integration Services Designer
    Version 14.0.1000.169

    Tuesday, May 21, 2019 9:22 PM

All replies

  • Hi TBQ,

    Could you please share the columns in your Sort Transformation and the data type of the sorted columns?

    Please try to recreate the same package to see if Sort Transformation still lost records.

    Please refer to the similar issue SSIS Data loss after sort .

    Best Regards,

    Mona Lv


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Wednesday, May 22, 2019 2:37 AM
  • Hi TBQ,

    May I know if you have anything to update?

    Best Regards,

    Mona Lv


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Friday, May 24, 2019 9:52 AM
  • The Sort Transformation has 13 Input Columns (Input and Output Properties tab):

    1. DT_STR (1252) Length 10  - SortKeyPosition 1
    2. DT_STR (1252) Length 2
    3. DT_STR (1252) Length 3
    4. DT_STR (1252) Length 3
    5. DT_STR (1252) Length 3
    6. DT_STR (1252) Length 10
    7. DT_STR (1252) Length 8
    8. DT_I8
    9. DT_I8
    10. DT_I4
    11. DT_I4
    12. DT_I4
    13. DT_DATE

    The data flow is not quite trivial, and I'm working with more than 70 different packages right now, so I'm not sure when I can get back to this.

    Is it possible I'm seeing the recurrence of the old bug - the Sort is feeding directly into a Merge Join?

    Friday, May 31, 2019 6:10 PM
  • Hi TBQ,

    If the data is string data, both the Merge and Merge Join transformations expect the string values to have been sorted by using Windows collation. To provide string values to the Merge and Merge Join transformations that are sorted by using Windows collation, use the following procedure.

    To provide string values that are sorted by using Windows collation
    Use a Sort transformation to sort the data.

    The Sort transformation uses Windows collation to sort string values.

    -or-

    Use the Transact-SQL CAST operator to first cast varchar values to nvarchar values, and then use the Transact-SQL ORDER BY clause to sort the data.

    Please refer to Sort Data for the Merge and Merge Join Transformations

    Best Regards,

    Mona Lv


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Monday, June 3, 2019 9:54 AM
  • ??? My question was "Sort transformation drops rows?" and in so many words: WHY?
    Monday, June 3, 2019 2:10 PM
  • Hi TBQ,

    MaximumThreads should be between 1 and 64, inclusive or -1 to default to the number of CPUs.

    It seems that your thread number exceeds the the number of CPUs.

    Best Regards,

    Mona Lv


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Tuesday, June 4, 2019 3:16 AM