SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


SSIS import


SSIS import

Author
Message
frecal
frecal
SSC Veteran
SSC Veteran (248 reputation)SSC Veteran (248 reputation)SSC Veteran (248 reputation)SSC Veteran (248 reputation)SSC Veteran (248 reputation)SSC Veteran (248 reputation)SSC Veteran (248 reputation)SSC Veteran (248 reputation)

Group: General Forum Members
Points: 248 Visits: 287
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
Jack Corbett
  Jack Corbett
SSC-Forever
SSC-Forever (46K reputation)SSC-Forever (46K reputation)SSC-Forever (46K reputation)SSC-Forever (46K reputation)SSC-Forever (46K reputation)SSC-Forever (46K reputation)SSC-Forever (46K reputation)SSC-Forever (46K reputation)

Group: General Forum Members
Points: 46039 Visits: 14925
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
At best you can say that one job may be more secure than another, but total job security is an illusion. -- Rod at work

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
John Rowan
John Rowan
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14030 Visits: 4591
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
frecal
frecal
SSC Veteran
SSC Veteran (248 reputation)SSC Veteran (248 reputation)SSC Veteran (248 reputation)SSC Veteran (248 reputation)SSC Veteran (248 reputation)SSC Veteran (248 reputation)SSC Veteran (248 reputation)SSC Veteran (248 reputation)

Group: General Forum Members
Points: 248 Visits: 287
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
John Rowan
John Rowan
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14030 Visits: 4591
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
frecal
frecal
SSC Veteran
SSC Veteran (248 reputation)SSC Veteran (248 reputation)SSC Veteran (248 reputation)SSC Veteran (248 reputation)SSC Veteran (248 reputation)SSC Veteran (248 reputation)SSC Veteran (248 reputation)SSC Veteran (248 reputation)

Group: General Forum Members
Points: 248 Visits: 287
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
John Rowan
John Rowan
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14030 Visits: 4591
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
frecal
frecal
SSC Veteran
SSC Veteran (248 reputation)SSC Veteran (248 reputation)SSC Veteran (248 reputation)SSC Veteran (248 reputation)SSC Veteran (248 reputation)SSC Veteran (248 reputation)SSC Veteran (248 reputation)SSC Veteran (248 reputation)

Group: General Forum Members
Points: 248 Visits: 287
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
)
John Rowan
John Rowan
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14030 Visits: 4591
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
frecal
frecal
SSC Veteran
SSC Veteran (248 reputation)SSC Veteran (248 reputation)SSC Veteran (248 reputation)SSC Veteran (248 reputation)SSC Veteran (248 reputation)SSC Veteran (248 reputation)SSC Veteran (248 reputation)SSC Veteran (248 reputation)

Group: General Forum Members
Points: 248 Visits: 287
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?
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search