For each row of a table I'm transforming I want to write the row to table A
and insert a row in table B and put the value of an identity column on table
B into a column on table A.

I can get a lookup to do the insert into table B but try as I may I can't
get it to return @@IDENTITY.

Lookup looks like

INSERT INTO TableB
(Type)
VALUES ('RA')
SELECT @@IDENTITY AS 'ID'
or SELECT ID FROM TableB WHERE (ID = @@IDENTITY)

Transformation calls lookup with

DTSDestination("ID") = DTSLookUps("LookupB").Execute()
This throws and invalid data type error.

I've tried using EXECUTE to call a stored procedure.
I've tried breaking it into two lookups.
What am I missing?

TIA
Steve

For anyone struggle with using lookups.
My query generated a double message
(1 row(s) affected)
(1 row(s) affected)
which seems to offend DTS.
inserting the line
SET NOCOUNT ON;
in the beginning of the script fixed it.

"runningdog" <runningdog (AT) reply (DOT) to.newsgroup> wrote