Post reply

How to compare current row with previous row

  • October 21, 2012 at 6:41 am

    #269946

    I'm not sure how this can be done in SSIS, but I would like to compare the current row to the previous row. My goal is to identify a break when the first field on the record changes, else if it's duplicated then route that record differently. I thought a variable would be needed to hold the previous value and use that variable to compare to the current variable, but I'm not sure how this could be done? If using the variable is the right approach how do you set the variable with current value.

  • October 21, 2012 at 7:00 am

    #1551154

    Yes it can be done. Have a look here for some pointers.

    I'm assuming that you are using a flat file source and not SQL Server? Otherwise there may be a better way (by using an appropriate query as your source).

    The absence of evidence is not evidence of absence.
    Martin Rees

    You can lead a horse to water, but a pencil must be lead.
    Stan Laurel

  • October 21, 2012 at 5:21 pm

    #1551195

    Thank You. I got it to work.

  • October 21, 2012 at 8:16 pm

    #1551218

    bobd125 (10/21/2012)

    Thank You. I got it to work.

    What are you using to guarantee the order of the "records" you're working with?

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • October 22, 2012 at 5:19 pm

    #1551686

    I'm using the SORT transformation.

  • October 23, 2012 at 12:50 am

    #1551741

    bobd125 (10/22/2012)

    I'm using the SORT transformation.

    Just a quick note on this. If you can avoid using the SORT transformation, I recommend that you do - it's a blocking component and performance is not good if you have a lot of data.

    If your source is SQL Server (or another RDBMS), it's almost certainly going to be better for you to do the sort at source (using ORDER BY).

    The absence of evidence is not evidence of absence.
    Martin Rees

    You can lead a horse to water, but a pencil must be lead.
    Stan Laurel

  • October 25, 2012 at 5:01 am

    #1552629

    Thank you for advice. In this case it's a flat file. I could start a new thread but since I've got your ear, I ran into another situation where I had to use a merge join with a SQL query and flat file. My Merge Join was in error because it the flat file wasn't sorted, so I had to throw a sorted between source and merge join. In this case the flat file was already in sorted order. Question: is there a what to tell SSIS that the flat file is already sorted to avoid having to add the Sort Transformation.

    Thanks again.

  • October 25, 2012 at 7:02 am

    #1552673

    bobd125 (10/25/2012)

    Thank you for advice. In this case it's a flat file. I could start a new thread but since I've got your ear, I ran into another situation where I had to use a merge join with a SQL query and flat file. My Merge Join was in error because it the flat file wasn't sorted, so I had to throw a sorted between source and merge join. In this case the flat file was already in sorted order. Question: is there a what to tell SSIS that the flat file is already sorted to avoid having to add the Sort Transformation.

    Thanks again.

    It's possible. Have a look here for more detail than I can type out, particularly the section on setting the sort attributes manually.

    The absence of evidence is not evidence of absence.
    Martin Rees

    You can lead a horse to water, but a pencil must be lead.
    Stan Laurel

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply