August 10, 2009 at 2:31 am
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.
Hope this makes sense
regards
Fre
August 10, 2009 at 2:15 pm
I need some more information.
What SSIS components are you using?
What is the flow of your SSIS package?
How are you getting the data from your source and what is your source?
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
August 10, 2009 at 4:39 pm
Jack's got some good questions here. You have not told us much about what you are doing.
In general, you'll need 2 data flows. One to load the parent data and one to load the child data. Inside the child data flow, you'll want to use a lookup transformations to find the parent id.
August 10, 2009 at 5:44 pm
thanks for the Help
I am using SSIS 2008 to import data into Sequel Server. Source are text files
Not 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..
Is this any clearer
regards
Fred
August 11, 2009 at 9:33 am
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.
August 11, 2009 at 7:28 pm
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?
thanks for your help
Fred
August 12, 2009 at 9:30 am
Well, a business key would not be something that you'd need to add to the parent table. I asked if you had one, meaning, is there a natural or candidate key already in your parent table.
Can you post an example of your table DDL and a line or two from your text file (w/ column headers)?
August 13, 2009 at 7:56 pm
Hi John
I have tried to create a test scenario.
so basically i add the record to the main table so it generates the MLK.
I then would like to retrieve that MLK so i can add it to the events table.
does this make any sense?
I have the main table
CREATE TABLE [dbo].[tblMain](
[MLK] [int] IDENTITY(1,1) NOT NULL,
[RECID] [int] NULL,
[name] [nvarchar](50) NULL,
[sname] [nvarchar](50) NULL
) ON [PRIMARY]
and events table
CREATE TABLE [dbo].[tblEvent2](
[MLK] [int] NULL,
[RECID] [int] NULL,
[Source] [nvarchar](50) NULL,
[EventType] [varchar](50) NULL,
[Alternate_ClientID] [int] NULL,
[Status] [smallint] NULL
)
August 14, 2009 at 10:12 am
Besides the MLK identity column, is there any way to uniquely identify rows? What is RECID?
August 14, 2009 at 8:12 pm
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?
August 17, 2009 at 12:27 pm
So RECID is unique? If so, I assume you are getting that in your file? Create 2 data flows. The first to load your parent table. The second can use RECID to lookup the parent MLK value as it loads the child table. You won't need to store the RECID in both tables.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply