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 Friday, August 17, 2012 8:21 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/17/2012)
If you mean that your query is loading a staging table, that isn't what I am talking about. I am talking about copying the data from the source tables to staging tables that mirror the source tables. The difference being that you perform the cleanup on the data as you move it. Then your query uses those tables, properly indexed.



Lynn, you mean mirroring the whole database and modify as like as we want and get used to work with that..?

and fYI, this is not only the query that i am getting struggled...so i want to mirror more or like whole database....but it is around 100 GB of size....


Thanks,
Charmer
Post #1346572
Posted Friday, August 17, 2012 8:26 AM


SSC-Insane

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

Group: General Forum Members
Last Login: Today @ 5:46 AM
Points: 22,472, Visits: 30,139
Charmer (8/17/2012)
Lynn Pettis (8/17/2012)
If you mean that your query is loading a staging table, that isn't what I am talking about. I am talking about copying the data from the source tables to staging tables that mirror the source tables. The difference being that you perform the cleanup on the data as you move it. Then your query uses those tables, properly indexed.



Lynn, you mean mirroring the whole database and modify as like as we want and get used to work with that..?


No, not mirroring the database. If I counted correctly your query uses 4 tables. Create 4 staging tables that are exact copies of the those source tables including column names, data types, etc. You then copy the data from the source tables to these staging tables cleaning up the data so that you don't need the LTRIM(RTRIM()) pairs in your main query.

Make sure that you properly index these staging tables to maximize performnace for your query and have your query read from those tables instead of the original source tables.





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 #1346573
Posted Friday, August 17, 2012 8:39 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/17/2012)
Charmer (8/17/2012)
Lynn Pettis (8/17/2012)
If you mean that your query is loading a staging table, that isn't what I am talking about. I am talking about copying the data from the source tables to staging tables that mirror the source tables. The difference being that you perform the cleanup on the data as you move it. Then your query uses those tables, properly indexed.



Lynn, you mean mirroring the whole database and modify as like as we want and get used to work with that..?


No, not mirroring the database. If I counted correctly your query uses 4 tables. Create 4 staging tables that are exact copies of the those source tables including column names, data types, etc. You then copy the data from the source tables to these staging tables cleaning up the data so that you don't need the LTRIM(RTRIM()) pairs in your main query.

Make sure that you properly index these staging tables to maximize performnace for your query and have your query read from those tables instead of the original source tables.




Lynn, i am just curious..... In case if we use exact copies of data types.....still we will not be able to clear the unwanted spaces right? because most of the columns are declared as char datatype....that is the main reason i am suffering with trims when i drag the data into staging table...


Thanks,
Charmer
Post #1346585
Posted Friday, August 17, 2012 9:00 AM


SSC-Insane

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

Group: General Forum Members
Last Login: Today @ 5:46 AM
Points: 22,472, Visits: 30,139
Charmer (8/17/2012)
Lynn Pettis (8/17/2012)
Charmer (8/17/2012)
Lynn Pettis (8/17/2012)
If you mean that your query is loading a staging table, that isn't what I am talking about. I am talking about copying the data from the source tables to staging tables that mirror the source tables. The difference being that you perform the cleanup on the data as you move it. Then your query uses those tables, properly indexed.



Lynn, you mean mirroring the whole database and modify as like as we want and get used to work with that..?


No, not mirroring the database. If I counted correctly your query uses 4 tables. Create 4 staging tables that are exact copies of the those source tables including column names, data types, etc. You then copy the data from the source tables to these staging tables cleaning up the data so that you don't need the LTRIM(RTRIM()) pairs in your main query.

Make sure that you properly index these staging tables to maximize performnace for your query and have your query read from those tables instead of the original source tables.




Lynn, i am just curious..... In case if we use exact copies of data types.....still we will not be able to clear the unwanted spaces right? because most of the columns are declared as char datatype....that is the main reason i am suffering with trims when i drag the data into staging table...



I am sorry, but what part of cleaning the data from source to stage are you missing? While copying the data you remove the leading and trailing spaces from the data. This means when the data lands in the staging tables, you no longer have to worry about the leading and trailing spaces.



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 #1346600
Posted Friday, August 17, 2012 9:08 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, what about trailing spaces.....? they are char data type .....so if data is less than the exact length, automatically the reamining length will occupied with empty spaces right? ...then i must use trim...don't i have to?

Thanks,
Charmer
Post #1346603
Posted Friday, August 17, 2012 9:20 AM


SSC-Insane

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

Group: General Forum Members
Last Login: Today @ 5:46 AM
Points: 22,472, Visits: 30,139
Charmer (8/17/2012)
Lynn, what about trailing spaces.....? they are char data type .....so if data is less than the exact length, automatically the reamining length will occupied with empty spaces right? ...then i must use trim...don't i have to?


If that is a concern, then use varchar/nvarchar data types in the staging tables. Not a big deal unless the trailing spaces are significant for any reason.




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 #1346608
Posted Friday, August 17, 2012 9:47 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/17/2012)
Charmer (8/17/2012)
Lynn, what about trailing spaces.....? they are char data type .....so if data is less than the exact length, automatically the reamining length will occupied with empty spaces right? ...then i must use trim...don't i have to?


If that is a concern, then use varchar/nvarchar data types in the staging tables. Not a big deal unless the trailing spaces are significant for any reason.



yes Lynn, that's what i am doing....since source is char datatype, i use Trim and pushing into staging table having varchar data type....


Thanks,
Charmer
Post #1346629
Posted Thursday, December 12, 2013 2:26 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, December 13, 2013 1:59 AM
Points: 19, Visits: 51
Hi

I have blogged about ranking over partitions in SSIS here (using a code transform)
http://paultebraak.wordpress.com/2013/02/25/rank-partitioning-in-etl-using-ssis/

Regards,
Paul
Post #1522484
Posted Friday, December 13, 2013 1:44 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, April 11, 2014 2:32 AM
Points: 128, Visits: 182
ptebraak (12/12/2013)
Hi

I have blogged about ranking over partitions in SSIS here (using a code transform)
http://paultebraak.wordpress.com/2013/02/25/rank-partitioning-in-etl-using-ssis/

Regards,
Paul


Thank you ptebraak....I will check this one
Post #1522598
« Prev Topic | Next Topic »

Add to briefcase «««123

Permissions Expand / Collapse