Row_Number() in SSIS?

Click here to monitor SSC

<a href="http://g.adspeed.net/ad.php?do=clk&zid=15220&wd=468&ht=60&pair=as" target="_blank"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15220&wd=468&ht=60&pair=as" alt="i" width="468" height="60"/></a>

SQLServerCentral is supported by Red Gate Software Ltd.

Log in :: Register :: Not logged in

<a href="http://g.adspeed.net/ad.php?do=clk&zid=15491&wd=120&ht=300&pair=as" target="_top"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15491&wd=120&ht=300&pair=as" alt="i" width="120" height="300"/></a>

Recent Posts

Popular Topics

Home

Home » SQL Server 2005 » SQL Server 2005 Integration Services » Row_Number() in SSIS?

Add to briefcase

27 posts, Page 1 of 3

Row_Number() in SSIS?

Expand / Collapse

Author

Message

Posted Tuesday, August 14, 2012 10:13 AM

Say Hey Kid

Group: General Forum Members
Last Login: Monday, February 18, 2013 2:08 AM
Points: 672, Visits: 725

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

Group: General Forum Members
Last Login: Today @ 3:27 PM
Points: 21,832, Visits: 27,845

And the question is, what?

Cool

Cool

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

Group: General Forum Members
Last Login: Monday, February 18, 2013 2:08 AM
Points: 672, Visits: 725

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

Smile

Thanks,
Charmer

Post #1344854

Posted Tuesday, August 14, 2012 10:28 AM

SSC-Insane

Group: General Forum Members
Last Login: Today @ 3:27 PM
Points: 21,832, Visits: 27,845

Have you tried tuning your SQL query?

Cool

Cool

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

Group: General Forum Members
Last Login: Monday, February 18, 2013 2:08 AM
Points: 672, Visits: 725

Lynn Pettis (8/14/2012)

Have you tried tuning your SQL query?

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

Sad

Thanks,
Charmer

Post #1344865

Posted Tuesday, August 14, 2012 10:35 AM

SSC-Insane

Group: General Forum Members
Last Login: Today @ 3:27 PM
Points: 21,832, Visits: 27,845

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

Sad

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

Cool

Cool

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

Group: General Forum Members
Last Login: Monday, February 18, 2013 2:08 AM
Points: 672, Visits: 725

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

Sad

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

Group: General Forum Members
Last Login: Today @ 9:53 PM
Points: 4,315, Visits: 9,647

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:

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 #1344875

Posted Tuesday, August 14, 2012 10:48 AM

Say Hey Kid

Group: General Forum Members
Last Login: Monday, February 18, 2013 2:08 AM
Points: 672, Visits: 725

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

Matt Miller (#4)

Posted Tuesday, August 14, 2012 10:48 AM

SSCertifiable

Group: General Forum Members
Last Login: Today @ 8:45 PM
Points: 7,002, Visits: 13,999

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

Sad

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

27 posts, Page 1 of 3

Permissions

Expand / Collapse