How can I use the auto-generated Id of a record I added in a subsequent task?

Question

I am currently adding some records to a table using the OLE DB Destination. Each record has an auto-generated Id field. I want to use this generated Id field as a foreign key in some child records.

I thought I would be able to a data flow line from one OLE DB Destination component to another, but this is not supported.

I would have thought this was a common problem - how do others solve it?

Answer 1

I ended up using the approach described here:

• How to insert scope_identity from parent table into the child table using SSIS

In my case it looked a bit like this:

INSERT INTO dbo.Benefit
 (PeriodId,
  BenefitCode,
  ...)
VALUES (
  ?,
  ?,
  ...);

SELECT ? = SCOPE_IDENTITY()