none
SSIS Data loss after sort RRS feed

  • Question

  • Hi Expert,

    Please assist with below. I am new at using SSIS and experiencing data loss after sorting. I have enabled the data viewer as well, but cannot seem to figure out why this is happening. The input count is a lot more than the output count.

    Thanks,

    Wynand

    Monday, November 13, 2017 9:49 AM

Answers

  • Can you try one thing?

    Create a new test package with some sample data from same file, attach sort transform on it and see if its repeating?

    If it still repeats, please share us the test package for further analysis


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    Tuesday, November 14, 2017 12:10 PM

All replies

  • Hi Wynand,

    I have never heard data loss is possible due to sorting other than if you enabled removing duplicates:

    Click the remove rows option and choose OK

    If this is not that, please share facts and how to reproduce.


    Arthur

    MyBlog


    Twitter

    Monday, November 13, 2017 2:00 PM
    Moderator
  • you have to give more information

    Where are you doing the sort and how are you experience data loss?

    Are you doing some kind of join operation using merge join or using merge /union all?


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    Monday, November 13, 2017 2:45 PM
  • Hi All,

    My apologies, I was not able to share anything further as it was my first post. It seems that I am still not able to post screenshots.

    However, the remove duplicates tick box is NOT ticked. Yes I have a merge join after the sort, but the data loss happens before that. 

    I have added a data viewer before and after the sort, and about 1/3rd of the data seems to be dropped. 

    I am trying to get a screenshot posted here and will do that as soon as they allow me.

    Any ideas?

    Thanks,

    Wynand

    Tuesday, November 14, 2017 6:39 AM
  • Assuming you're using sort transformation, is your data of unicode or accent sensitive collation?

    Would it be possible for you to share some sample data which is getting missed?

    Also please show us current flow of the package


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    Tuesday, November 14, 2017 6:49 AM
  • Hi Wynand.Cilliers,

    If you manually set the IsSorted property of the output to True and the data is not sorted, there might be missing data or bad data comparisons in the downstream Merge or Merge Join transformation when you run the package.

    For more information, please check: Setting Sort Options on the Data

    Then one option is you could avoid the sort component.

    Check if this helps.

    Regards,

    Pirlo Zhang


    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, November 14, 2017 9:17 AM
    Moderator
  • Hi All,

    My data is from a txt flat file, pipe delimited. Not sure what the collation is. How do I check that?

    I use the sort transformation, basically on date and product code (transnational data). I cannot however share the data itself. I am still not able to add screenshots here, until such time MS verifies my account...

    Lets try this: https://www.dropbox.com/sh/ly0alsd1882vj4m/AAD4uz5yBYUtBLvQOaLqydsXa?dl=0

    Thanks,

    Wynand

    Tuesday, November 14, 2017 11:27 AM
  • Are you having blanks or missing values for any of the two columns (TxnDate,Currency) in your flat file?

    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    Tuesday, November 14, 2017 11:43 AM
  • Hi Visakh,

    No, there are not blanks or missing values in any of the columns. I honestly don't understand why this is happening.

    Thanks,

    Wynand

    Tuesday, November 14, 2017 11:57 AM
  • Can you try one thing?

    Create a new test package with some sample data from same file, attach sort transform on it and see if its repeating?

    If it still repeats, please share us the test package for further analysis


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    Tuesday, November 14, 2017 12:10 PM
  • Thanks Visakh,

    After trying to recreate the problem, I realized that the reason why it was a different number of rows from input into sort and the output, was because the package stopped further down the flow, and was not actually dropping lines, it just stops processing at about 65K records....

    After correcting the problem further down the flow, the package now runs through all 94K transactions.

    Thanks for your help though!

    Much appreciated!

    Wynand 

    Tuesday, November 14, 2017 12:45 PM
  • Thanks Visakh,

    After trying to recreate the problem, I realized that the reason why it was a different number of rows from input into sort and the output, was because the package stopped further down the flow, and was not actually dropping lines, it just stops processing at about 65K records....

    After correcting the problem further down the flow, the package now runs through all 94K transactions.

    Thanks for your help though!

    Much appreciated!

    Wynand 

    Cool

    Glad that you got it sorted out


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    Tuesday, November 14, 2017 1:08 PM