How to compare current row with previous row

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » SQL Server 2005 Integration Services » How to compare current row with previous row

How to compare current row with previous row

Rate Topic

Display Mode

Topic Options

Author

Message

bobd125

Posted Sunday, October 21, 2012 6:41 AM

SSC Rookie

Group: General Forum Members
Last Login: Today @ 4:08 AM
Points: 25, Visits: 170

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.

Post #1375167

Phil Parkin

Posted Sunday, October 21, 2012 7:00 AM

SSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 11:01 PM
Points: 4,319, Visits: 9,659

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).

____________________________________________________________________________________________

Help us to help you. For better, quicker and more focused answers to your questions, consider following the advice in this link:

http://www.sqlservercentral.com/articles/Best+Practices/61537/

If you are asking for help and your post does not contain a question, you should expect responses which do not contain any answers. Put a question mark in there somewhere - it's not rocket science.

Post #1375170

bobd125

Posted Sunday, October 21, 2012 5:21 PM

SSC Rookie

Group: General Forum Members
Last Login: Today @ 4:08 AM
Points: 25, Visits: 170

Thank You. I got it to work.

Post #1375215

Jeff Moden

Posted Sunday, October 21, 2012 8:16 PM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 9:01 PM
Points: 33,111, Visits: 27,037

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."

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Post #1375239

bobd125

Posted Monday, October 22, 2012 5:19 PM

SSC Rookie

Group: General Forum Members
Last Login: Today @ 4:08 AM
Points: 25, Visits: 170

I'm using the SORT transformation.

Post #1375787

Phil Parkin

Posted Tuesday, October 23, 2012 12:50 AM

SSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 11:01 PM
Points: 4,319, Visits: 9,659

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).

____________________________________________________________________________________________

Help us to help you. For better, quicker and more focused answers to your questions, consider following the advice in this link:

http://www.sqlservercentral.com/articles/Best+Practices/61537/

If you are asking for help and your post does not contain a question, you should expect responses which do not contain any answers. Put a question mark in there somewhere - it's not rocket science.

Post #1375856

bobd125

Posted Thursday, October 25, 2012 5:01 AM

SSC Rookie

Group: General Forum Members
Last Login: Today @ 4:08 AM
Points: 25, Visits: 170

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.

Post #1376917

Phil Parkin

Posted Thursday, October 25, 2012 7:02 AM

SSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 11:01 PM
Points: 4,319, Visits: 9,659

bobd125 (10/25/2012)

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

____________________________________________________________________________________________

Help us to help you. For better, quicker and more focused answers to your questions, consider following the advice in this link:

http://www.sqlservercentral.com/articles/Best+Practices/61537/

If you are asking for help and your post does not contain a question, you should expect responses which do not contain any answers. Put a question mark in there somewhere - it's not rocket science.

Post #1376965

« Prev Topic | Next Topic »

Permissions