I have one source table with field1, field2, field3 and field4. I need to put field1 and field2 in DestinationTable1, return (with @@Identity?) the DestinationTable1ID (which is a numeric identity column), and then put that value in DestinationTable2 (foreign key pointing to DestinationTable1), along with field3 and field4.
I am a beginner, I have tried everything I could think of. I could not execute stored procedures from within a Data Driven Query Task, maybe I'm on the wrong track completely. Im using an SQL server 2000 database. It has to be a DTS package.