Is there an easy method to retrieve a identity value during/after a row is
inserted in the DB-Destination Task.

1) Retrieve record with custom and address combined
2) Extract address from customer and create address record
(need AddressID - Identity column) after the insert
3) Create customer record with the AddressID (identity column) from previous
step

I would hope do due this without having to do a lookup
(address1,address2,city,state,zip) by retrieving the identity column value as
the record is inserted.

thanks,

There is no way to get IDENTITY Values like this. They way I work around
this is to assign the new row number prior to the insert. The logic goes
like this-

Exec SQL Task - SELECT MAX(IDCol) FROM Table - Store this in a variable.

Data Flow - Source - Row Number Transform - Destination

The Row Number transformation will accept a variable as the seed.

You will either remove the IDENTITY attribute from your base table, or turn
insert identity on.

Row Number Tranform is free from SQLIS (http://www.sqlis.com), or write your
own in a Script Component.

--
Darren Green
http://www.sqldts.com
http://www.sqlis.com


"Dave L" <DaveL (AT) discussions (DOT) microsoft.com> wrote