July 30, 2009 at 7:06 am
So... three days discussing this issue and counting, huh? 🙂
Wondering why my two simple questions never got an answer, they are:
a) Is there a purging or archiving strategy I could help by resorting to partitioning?
b) Are most queries having a general predicate that would perform better by resorting to partitioning?
We still talking about improving a once-a-day process that runs for 120 seconds, aren't we?
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.July 30, 2009 at 7:21 am
This has been a good topic 🙂
a) I will archive data, this will be history information. Any advice?
b) queries will be only to load data into the table and reporting for users.
beside to imporve 120seconds. We want to run the load process with pc resources instead SQL resources..
July 30, 2009 at 9:14 am
I tried what you mention. But, seems like the data from the flat file go all the time to my Table1. The data should go to BulkTable and Table1.
Take a look at the print shot that attach
July 30, 2009 at 9:26 am
It looks like you guys start a bit earlier in the AM than I :-).
You're SSIS work is close. You need 2 data flows. The first data flow needs to contain the lookup where you'll redirect error rows to Table 1. The second data flow will lookup the values that you'll need from Table1 along with MSO and insert the rows into your DestinationTable.
You no longer need the BulkTable! The rows that you were loading into BulkTable are now in your data flow and will go directly to the Destination Table ready for consumption by the end user.
Remember, 2 data flows, one to load Table1 and one to Lookup values and load DestinationTable. Connect the 2 data flows in the Control Flow so Table1 gets loaded first.
As for the partititioning piece Paul, there is no doubt that partitioning the table will help with purging and querying, but the requirement here is to perform the load (regardless of how long it is taking) with minimal load on the DB by off-loading the file processing from the database engine as much as possible.
July 30, 2009 at 10:03 am
ah! that's why I got errors.. mmmh, it makes sense then.
I just did 3 Data Flow, the first one was the Bulk table, the second one insert into the Table1 and 3rd to destination...
But, you are right, I just need to match the flat file with the tables and get the information.. Let me try that and Let you know 🙂
Thank you 🙂
July 30, 2009 at 10:21 am
Wait a second..
for my destination table I will need to join Table1, MSo and BulkTable..
If Im not using a bulktable, then it should be Table1, MSo and Flat File ????? is this posible ?
July 30, 2009 at 11:22 am
Yes. If you go back to my detailed post, you'll see that I discussed using Lookup transformations in place of the joins to get the values from MSO and Table1 into your data flow.
Doing this all in the data flow is what removed the need to stage the data in your BulkTable first, which will reduce the overhead of the import on the database engine.
July 30, 2009 at 2:56 pm
How I can read my values from the Flat File in my second Data Flow ? when I do my Join (lookup), my MSO table join only with a value from the Flat file. (same thing with my Table1)
I know this is basic and simple. I never used SSIS before, other than flat files into a table. I really appreciate your long-suffering with this matter
July 30, 2009 at 3:18 pm
MTY (7/30/2009)
How I can read my values from the Flat File in my second Data Flow ?
The same way you do in the first data flow, use a Flat File Source to re-read the original file.
July 30, 2009 at 3:25 pm
I found something interesting in my first Data Flow.
since my Table1 has an Indetity Key Column, when the lookup redirect the Error output (or data that doesnt exist). My Identity Key column is not progressive. Seems like the process insert the data into the table and then just keep those that dont exist (deleting the duplicates)..
so the 1st Data Flow is:
flat file -- lookup (reading the Table1) --(error output) Destination table (Table 1)
July 30, 2009 at 3:35 pm
Yes, that is your Data Flow 1. I'm not sure I'm following you with what you're saying though. Is that now working for you?
July 30, 2009 at 8:56 pm
Hahahaha, I'm just making fun of myself. This is easier than I thought. I have changed the SSIS like 5 times. I had the flat file in my second data flow. But, when I got your post, I thought you meant something different. I was trying to put the information in variables or another flat file. I wanted to understand what you meant. But, I have learned different ways to do a very simple SSIS package.
About my first data flow, it works, but this is what it happens after the data is into Table1:
Table 1 has a Identity Column (primary key).
Table1
( PKidentity (1,1)
, macvchar(5) )
Table1
100130
234500
365790
Flat File ( .csv file)
North, 00130, 23, 00:23, ….. etc
North, 00122, 34, 00:25, …..etc
If u see, the first row of the flat file has a mac (00130) that already exists in the table. So, the data flow it will insert the second row (00122) into table1.
If I select my table after the insertion you will see this:
100130
234500
365790
500122
When I run the package, process shows the rows that will move into the Table1 (the counting rows), so I see only 1 record go as an Error output, but my Primary Key (identity) doesn’t go in order.
For me, looks like the process inserted both record from the flat file and then delete that one that exist in the Table1 already.
What do you think??
August 3, 2009 at 3:18 pm
I tried to duplicate this will no luck. I set up a table with an identity column and ran 2 rows through my data flow. One row already existed in my table, another did not. It inserted one row and kept the identity values in order. I even went on to add more rows via SSMS and all identity values show up in the correct sequence.
August 28, 2009 at 9:39 am
Hi,
Sorry I took time to replay. Ive a question about your post. If I do 52 logical partitions, I will have data from different years. I mean, week 1 will contain all the records of the first week of January of 2006, 2007, 2008, 2009, etc... Its anyway to do a Partition with Year and Week? If so how? ... Or what is your best opinion about this
Regards,
MTY
August 31, 2009 at 8:48 am
Yes, you definately want to use year/week and not have weeks from different years in a partition. Here's a great article on table partitioning that uses a sliding window date partition example.
http://msdn.microsoft.com/en-us/library/ms345146(SQL.90).aspx
Viewing 15 posts - 31 through 45 (of 45 total)
You must be logged in to reply to this topic. Login to reply