2 Destination tables, with an ID field in one

Question

2 Destination tables, with an ID field in one

Shark Energy

SSCrazy Eights

Points: 8389
More actions
November 21, 2008 at 5:52 am

#201652

Hi all,
Have a package where I move data in from a csv, run it through various lookups, and then create an "Items" record in my db. But for each "Items" record (that gets given an identity value) I need to populate an ItemsComments table.
Is there built in functionality to do this? My initial thoughts are that I will have to populate a holding "Items" table, and then create from there or something.
Thanks,
Shark

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Tim Mitchell SSCoach Points: 15685 More actions · Answer 1

Shark Energy (11/21/2008)
Hi all,
Have a package where I move data in from a csv, run it through various lookups, and then create an "Items" record in my db. But for each "Items" record (that gets given an identity value) I need to populate an ItemsComments table.
Is there built in functionality to do this? My initial thoughts are that I will have to populate a holding "Items" table, and then create from there or something.
Thanks,
Shark

I'm assuming that your ItemsComments records are already related to the Items records in your source system? If that's the case, populate your Items table first in its own data flow. In another data flow that follows the first (not run in parallel), include a lookup on the Items table you just populated to retrieve the new identity value and include that value as the FK value on the ItemsComments table.

hth,

Tim

Tim Mitchell
TimMitchell.net | @tmitch.net | Tyleris.com
ETL Best Practices

Shark Energy SSCrazy Eights Points: 8389 More actions · Answer 2

Good plan. So I'll have to make sure I have a related field in the Items table to connect on the Comments table in order to test. I guess I can look up on a temp id column or something.

Cheers

Shark

Phil Parkin SSC Guru Points: 246979 More actions · Answer 3

Just use a lookup from your source 'Item' data to retrieve the ID of the newly created Item and then use that to populate your ItemComments. No need for the temp key - this is the whole point of creating the Item entries first.

Phil

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel