Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

SSIS import Expand / Collapse
Author
Message
Posted Monday, August 10, 2009 2:31 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, April 15, 2013 10:34 PM
Points: 44, Visits: 263
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
Post #767676
Posted Monday, August 10, 2009 2:15 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 6:23 PM
Points: 11,247, Visits: 13,009
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

Applications Developer

Don't let the good be the enemy of the best. -- Paul Fleming

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
How to Post Performance Problems
Crosstabs and Pivots or How to turn rows into columns Part 1
Crosstabs and Pivots or How to turn rows into columns Part 2
Post #768174
Posted Monday, August 10, 2009 4:39 PM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Tuesday, August 19, 2014 10:01 AM
Points: 3,844, Visits: 3,841
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.




John Rowan

======================================================
======================================================
Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
Post #768262
Posted Monday, August 10, 2009 5:44 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, April 15, 2013 10:34 PM
Points: 44, Visits: 263
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
Post #768284
Posted Tuesday, August 11, 2009 9:33 AM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Tuesday, August 19, 2014 10:01 AM
Points: 3,844, Visits: 3,841
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.



John Rowan

======================================================
======================================================
Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
Post #768728
Posted Tuesday, August 11, 2009 7:28 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, April 15, 2013 10:34 PM
Points: 44, Visits: 263
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
Post #769053
Posted Wednesday, August 12, 2009 9:30 AM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Tuesday, August 19, 2014 10:01 AM
Points: 3,844, Visits: 3,841
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)?




John Rowan

======================================================
======================================================
Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
Post #769413
Posted Thursday, August 13, 2009 7:56 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, April 15, 2013 10:34 PM
Points: 44, Visits: 263
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
)

Post #770624
Posted Friday, August 14, 2009 10:12 AM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Tuesday, August 19, 2014 10:01 AM
Points: 3,844, Visits: 3,841
Besides the MLK identity column, is there any way to uniquely identify rows? What is RECID?



John Rowan

======================================================
======================================================
Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
Post #771094
Posted Friday, August 14, 2009 8:12 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, April 15, 2013 10:34 PM
Points: 44, Visits: 263
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?
Post #771395
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse