How can I use SSIS to create an output row in one table, get the identity from that table for a new row in another table?

Question

Is there a way we can process rows from one input table to two output tables where the first output table has an identity field? We are using SQL Server 2008 SSIS. We need to post the record in the first output table to get the identity field int value to use when posting the second output table's row.

The example is Borrower and CoBorrower name and address in one input row going to two tables since the CoBorrower output table will be sparse. They will be linked when used later by the identity field in the Borrower table to an int field that is not an identity in the CoBorrower table.

Answer 1

Check this post out on how to generate surrogate keys using a script component : SSIS Talk Link

You could then use a multicast to create two separate date buffers that can be loaded into your two tables within the same df task.