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 123»»»

Row_Number() in SSIS? Expand / Collapse
Author
Message
Posted Tuesday, August 14, 2012 10:13 AM


Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Monday, March 31, 2014 2:54 AM
Points: 688, Visits: 751
Hi Friends,

I have a select statement having Row_number() over(partition by coulmn order by column) ....

i see no option in any of SSIS flow tasks doing this Row number partitioning...

if we can do this by script component, could you guys give me sample codings so that it would be very useful to me...

i am asking this since the T-SQL statement has 4 join statements and to process around 5 million records...

so i use this statement in OLEDB SQL Command(since i got no other option) ...so it is taking too much time to process.....

what i am wondering is , instead of using in the SQL command, if i just selec the records and process the partition in script component, it can help in speed up the performance....

So give me your valuable suggestions friends...

any suggestions would be really appreciated..


Thanks,
Charmer
Post #1344846
Posted Tuesday, August 14, 2012 10:14 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 9:53 PM
Points: 22,534, Visits: 30,310
And the question is, what?


Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1344848
Posted Tuesday, August 14, 2012 10:20 AM


Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Monday, March 31, 2014 2:54 AM
Points: 688, Visits: 751
Lynn Pettis (8/14/2012)
And the question is, what?


sorry, Lynn...i acciedntly pressed the enter button before completing hte question....i am sorry...


Thanks,
Charmer
Post #1344854
Posted Tuesday, August 14, 2012 10:28 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 9:53 PM
Points: 22,534, Visits: 30,310
Have you tried tuning your SQL query?



Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1344863
Posted Tuesday, August 14, 2012 10:31 AM


Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Monday, March 31, 2014 2:54 AM
Points: 688, Visits: 751
Lynn Pettis (8/14/2012)
Have you tried tuning your SQL query?


Yes, Lynn...my SQL query has no other way....this is it...


Thanks,
Charmer
Post #1344865
Posted Tuesday, August 14, 2012 10:35 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 9:53 PM
Points: 22,534, Visits: 30,310
Charmer (8/14/2012)
Lynn Pettis (8/14/2012)
Have you tried tuning your SQL query?


Yes, Lynn...my SQL query has no other way....this is it...


Not sure what you mean by "has no other way."



Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1344867
Posted Tuesday, August 14, 2012 10:37 AM


Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Monday, March 31, 2014 2:54 AM
Points: 688, Visits: 751
Lynn Pettis (8/14/2012)
Charmer (8/14/2012)
Lynn Pettis (8/14/2012)
Have you tried tuning your SQL query?


Yes, Lynn...my SQL query has no other way....this is it...


Not sure what you mean by "has no other way."


i mean....SQL query is fine...i meant no other option to tune sQL query...


Thanks,
Charmer
Post #1344870
Posted Tuesday, August 14, 2012 10:43 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 10:56 AM
Points: 4,832, Visits: 11,200
Sounds like you want to do the sort in SSIS rather than T-SQL - bad idea. One way or another, you should make the SQL engine do that work for you - it's at least an order of magnitude faster.

Just how were you proposing to do a sort in a script component, by the way?



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 #1344875
Posted Tuesday, August 14, 2012 10:48 AM


Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Monday, March 31, 2014 2:54 AM
Points: 688, Visits: 751
Phil Parkin (8/14/2012)
Sounds like you want to do the sort in SSIS rather than T-SQL - bad idea. One way or another, you should make the SQL engine do that work for you - it's at least an order of magnitude faster.

Just how were you proposing to do a sort in a script component, by the way?


sorry Phil, i thought there could be a way using script component....i generated sequence number using script component....but i was not sure that we cannot do sort using script component....but Phil, after you said...it is clear that sort in ssis is not possible unless using sql query doing in sql command....



Thanks,
Charmer
Post #1344880
Posted Tuesday, August 14, 2012 10:48 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 9:59 PM
Points: 7,090, Visits: 14,717
Charmer (8/14/2012)
Lynn Pettis (8/14/2012)
Charmer (8/14/2012)
Lynn Pettis (8/14/2012)
Have you tried tuning your SQL query?


Yes, Lynn...my SQL query has no other way....this is it...


Not sure what you mean by "has no other way."


i mean....SQL query is fine...i meant no other option to tune sQL query...


How much of a drag does the ROW_NUMBER() aspect put on query execution? It sounds like you already have a fairly hefty process.

As far as doing it in a script - you might care to use the auto-increment script as your basis:

http://www.ssistalk.com/2007/02/20/generating-surrogate-keys/

In your case - doing the lookup for a max value is not needed since you're cranking it out internally (so simply initialize the value to 1).

You would just have to add some condition to reset the increment back to 1 when you move out of the partition.



----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
Post #1344881
« Prev Topic | Next Topic »

Add to briefcase 123»»»

Permissions Expand / Collapse