Hello, I have 2 tables one to many relationship. with the unique key in the master table being an auto incrementing int field. I am looking at figureing out the best way of returning the id from the master table so i can alsod add the record to the child table so it maintains the relationship.

I am using SSIS 2008 to import data into Sequel Server. Source are text filesNot sure if this is best method of doing this but the auto int field in the parent table is the link between the 2 tables. So basically I add a record to the Parent table which creates the auto int field and then also wish to add a record to the child table to maintain the relationship. I need to retrieve that auto int field so that i can add it to the child table.

So in the SSIS i have a source and 2 destinations...record goes to parent table and also to child table..

So you're using an IDENTITY column as a surrogate key for your parent rows. Is there a business key available that you could use to find the parent row once created? If so, a simple lookup is what you'll want to do.

Yes thats right..is that good practice you reckon? If i have a business key i would add that to the main table and add that record then do a lookup before i add to the child table..is that what you mean?

yes that is unique and at the moment i store it in the main table then run an update routiine using it to retrieve the MLK. I thought it would be better to retrieve at same time as i add the record to the child table rather than run the routine against the full table. Does that make sense?