In an SSIS package, how do you insert master-detail records?

Question

I have a data flow task that extracts data from one database and inserts into a SQL Server database. After each record is inserted in the master row I also need to insert rows into a detail table. The data for the detail table is pretty simple and can be calculated.

1. How do I retrieve the value of the identity column after inserting?
2. How can I produce the rows that must be inserted in the second table? Can I do this in a script?

Do I need to use a Foreach loop at the control flow level which transfers the parent row in a data flow task, then have another Foreach loop which inserts the detail records?

Can I just perform all the detail row inserts in a script? That would probably be easier than putting in the Foreach loops.

Answer 1

Here's one approach..

Create a variable of type object.

Create a "Execute SQL Task" that grabs your source data and loads it into the variable (ADO.NET).

Create a "ForEach Loop Container.

Drag the Success connector (green) from the "Execute SQL Task" to the "ForEach Loop Container". Change Enumerator on the loop container to "foreach ADO Enumerator" and pick your variable from the "ADO object source variable".

Within your loop, you should be alble to add an "Execute SQL Task" that you can work with..

You should be able to use the SCOPE_IDENTITY() to get each ID after inserting into the master table and use that to insert into the detail table.