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.

Answer 2

I've had this issue, on a somewhat more massive scale (importing deeply-nested XML). I was able to take advantage of a feature of the XML Source, that creates a surrogate key in the master "table" that is then repeated in the "child" table as a "foreign key".

The idea is to allow each "table" to reach a separate staging table in the database. After all the rows have been processed, you can then use these "foreign keys" to do any final processing that requires both the master and detail rows to be created at the same time - even in a transaction if necessary.

This allows the performance of a data flow task, combined with the master-detail nature of your data.