I'm pretty new to DTS adn I have the following situation that I hope you can help with: -

I have 2 tables: -
Locations
 ID, sequential identity seed
 Code, nvarchar
 Desc, nvarchar
 AdminID, int

AdminDetails
 ID, sequential identity seed
 Street, nvarchar
 PostCode, nvarchar

I am bringing all the relevant data over in a CSV file.

The field AdminID on the Locations table is actaully the ID of the AdminDetails table. And hering lies my problem. I know how to insert into the 2 tables (using DTSTransformstat_InsertQuery for AdminDetails) but HOW do I get the @@IDENTITY from the inserted AdminDetails row into my Locations table?

I've scanned through as many on the posts as I can but can't see anything.

Hope you can help.

Cheers

Take a look at scope_identity in the books on line.

Rand

Rand,

Perect, that worked a treat, thanks mate.

For the benefit of others I'll detail my solution: -

A CSV file contianing the following fields: -

    LocCode, LocDesc, AdminID, Street, PostCode

(AdminID will be passed as NULL for each record and is included so I can use it in Row Transform)

I have 2 tables: -

Locations
    ID, sequential identity seed
    Code, nvarchar
    Desc, nvarchar
    AdminID, int

AdminDetails
    ID, sequential identity seed
    Street, nvarchar
    PostCode, nvarchar

Locations.AdminID is actualy the AdminDetails.ID generated when inserting into AdminDetails.

Create a Transform Data Task with the Destination as the TillLocations table.
Two lookups: -

Lookup("Insert")
    INSERT INTO AdminNameAddressDetails
                      (StreetDescriptor, Locality, PostCode, IsActive)
    VALUES (?, ?, ?, ?)

Lookup("IDENTITY")
    SELECT IDENT_CURRENT('AdminNameAddressDetails') AS Expr1

(Thanks Rand)

And a single ActivX Script Transformation for ALL fields :-

    Function Main()
        Dim oDummy
        DTSLookups("Insert").Execute DTSSource("Street"), DTSSource("PostCode")

        DTSDestination("AdminID") = DTSLookups("IDENTITY").Execute(DTSSource("AdminID" ))
        DTSDestination("Desc") = DTSSource("Desc")
        DTSDestination("Code") = DTSSource("Code")

        Main = DTSTransformStat_OK
    End Function

Hope this makes sense if not I'll try and explain it a bit better.

Thanks again Rand, I've been trying to solve this one for ages.

Adrian