Hi folks,

hope somebody can help me out since I am running mad on this and no book can
help!

I want to consolidate 8 adress db´s (Access,Paradox, txt ,Sybase SQL.etc) to
one big address DB on SQL Server using DTS

After importing my Source Tables look like this

tblSource:
Name
Firstname
BirthDate
Description
Street
Zip
....


My 2 destination tables which should hold all the information

tblAddress:
AddressID
Name
Name2
Street
Zip
....

1 : n Relation to

tblInfo:
ID
AddressID
Name
Info

Now I am trying to transform: Name -> Name ; Firstname -> Name2 etc.. no
Problem so far..

but I haven´t found a way to do the following :
set the relation tblInfo.AddressID = tblAddress.AddressID
tblSource.Description -> tblInfo.Info
tblInfo.Name -> "Description"


Cheers

Adrian

You can only have one destination table for the DataPump task, but you
can use lookups to call separate insert statements from within an
ActiveX Script transform.

Or

Insert into one flat table, and then use regular SQL to load the data
into the final tables.


--
Darren Green (SQL Server MVP)
DTS - http://www.sqldts.com

PASS - the definitive, global community for SQL Server professionals
http://www.sqlpass.org

Thats a good Idea but how do I get the Identity value from my parent row?

Thanks a lot !

Adrian

"Darren Green" <darren.green (AT) reply-to-newsgroup-sqldts (DOT) com> schrieb im
Newsbeitrag news:BZy1MeEnw6tCFw60 (AT) sqldts (DOT) com...

In message <#Rvv2SAeFHA.544 (AT) TK2MSFTNGP12 (DOT) phx.gbl>, Adrian
<Adrian.Porger (AT) gmx (DOT) de> writes
Make the parent row insert through a Lookup. The lookup can be an insert
and a select, the latter for SCOPE_IDNTITY(). Make sure you SET NOCOUNT
ON in the lookup. Might be easier to wrap this in a porc through.

--
Darren Green (SQL Server MVP)
DTS - http://www.sqldts.com

PASS - the definitive, global community for SQL Server professionals
http://www.sqlpass.org