Howdy all. Im wondering if theres a way to do the
following:


Source .txt file;
col1
col2
col3
col4

Destination table1;
IDCol Identity column
col1
col2

Destination table2;
IdCol value from table1
col3
col4

The problem is getting the ID value for table2
I can use either a Global Variable or a Lookup Value but
the problem is that it will only pass back one value. How
do I insert one row to table1, get the Id, insert 1 row
into table2 including that Id... and so on?


TIA, Chris

How about

Insert the col1 and col2 into table1 in one datapump

For table2 load then you compare the col1 and col2 values in table1 with
those in the text file and load the appropraite identity value from a
lookup.

--
--

Allan Mitchell (Microsoft SQL Server MVP)
MCSE,MCDBA
www.SQLDTS.com
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org

"chris" <anonymous (AT) discussions (DOT) microsoft.com> wrote

Now Im really lost. The identity value doesnt exist in the
text file so I cant compare the tables to it. Am I missing
something?

Ahhhhhhhhh

OK

A Datapump is the act of moving data from source to destination. Sorry for
the terminology.

1. Pump Col1 and Col2 into Table1. This will give you your identity values
in table1 yes?
2. Now pump col3 and col4 into Table2. To get the identity value from
table1 you can perform a lookup passing in col1 and col2 and returning the
ID column

Have you read this article

How to Use Lookups in DTS
(http://www.sqldts.com/default.aspx?277)

--
--

Allan Mitchell (Microsoft SQL Server MVP)
MCSE,MCDBA
www.SQLDTS.com
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org

"chris" <anonymous (AT) discussions (DOT) microsoft.com> wrote