August 21, 2009 at 9:49 am
Hi,
I have a flat file which is to be loaded into multiple tables -tableA,tableB and tableC at destination.
There is foreign key in tableB and tableC which is referring to tableA.
Thus, I have to populate tableA before I populate tableB and tableC, and this has to be done in a single dataflow task.
Please help.
Thanks in advance.
August 21, 2009 at 9:50 am
Load it into a staging table, and build a proc that handles it from there.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
August 21, 2009 at 9:55 am
Staging table as in temporary table in the Database and then update tableB and tableC ?
August 21, 2009 at 11:14 am
Staging table as in physical table or a temporary table ?
Which one would work efficiently ?
Please reply.
Thanks
August 21, 2009 at 3:18 pm
Not a lot of difference as far as efficiency. Where possible, I prefer persisted staging tables - I've found that it makes development and troubleshooting a bit easier.
hth,
Tim
Tim Mitchell, Microsoft Data Platform MVP
Data Warehouse and ETL Consultant
TimMitchell.net | @Tim_Mitchell | Tyleris.com
ETL Best Practices
August 21, 2009 at 3:36 pm
I agree with Tim. I leave the loaded data in the staging table after the package run and do not delete it until that next run. Tha way if that are problems, I can always go look at the actual data contents.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
August 22, 2009 at 4:59 am
Or if your flat file already contains a PK, you could multicast to all the tables and then run UPDATE queries at the end to set the FKs, using the source PK as the 'link' field in the UPDATE query. Avoids the need for a staging table.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
August 24, 2009 at 6:50 am
touchmeknot123 (8/21/2009)
Staging table as in temporary table in the Database and then update tableB and tableC ?
It can be done with a temp table. That's how I generally handle these.
I use OpenRowset and Select Into to create the temp table with data from the file, then insert into the primary table, and use the Output clause of the insert to grab all of the PKs, and then join that back to the temp table and use the result from that to populate the sub-tables.
It's a very efficient process. The main thing you have to watch out for is making sure you have enough room in tempdb if the file is large. That's easy enough to do, but you do have to make sure of it.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
August 24, 2009 at 10:56 am
Thanks for pouring in your ideas
August 26, 2009 at 7:36 am
You're welcome.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply