Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

How to compare current row with previous row Expand / Collapse
Author
Message
Posted Sunday, October 21, 2012 6:41 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, March 21, 2014 2:32 PM
Points: 25, Visits: 178
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
Posted Sunday, October 21, 2012 7:00 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 11:45 AM
Points: 5,254, Visits: 12,178
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.

When you ask a question (and please do ask a question: "My T-SQL does not work" just doesn't cut it), please provide enough information for us to understand its context.
Post #1375170
Posted Sunday, October 21, 2012 5:21 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, March 21, 2014 2:32 PM
Points: 25, Visits: 178
Thank You. I got it to work.
Post #1375215
Posted Sunday, October 21, 2012 8:16 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 12:06 PM
Points: 35,572, Visits: 32,164
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1375239
Posted Monday, October 22, 2012 5:19 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, March 21, 2014 2:32 PM
Points: 25, Visits: 178
I'm using the SORT transformation.
Post #1375787
Posted Tuesday, October 23, 2012 12:50 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 11:45 AM
Points: 5,254, Visits: 12,178
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.

When you ask a question (and please do ask a question: "My T-SQL does not work" just doesn't cut it), please provide enough information for us to understand its context.
Post #1375856
Posted Thursday, October 25, 2012 5:01 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, March 21, 2014 2:32 PM
Points: 25, Visits: 178
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
Posted Thursday, October 25, 2012 7:02 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 11:45 AM
Points: 5,254, Visits: 12,178
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.



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

When you ask a question (and please do ask a question: "My T-SQL does not work" just doesn't cut it), please provide enough information for us to understand its context.
Post #1376965
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse