In the SSIS package, how do you insert master part records?

Question

I have a data flow task that retrieves data from a single database and inserts it into a SQL Server database. After each record is inserted into the main row, I also need to insert the rows into the detail table. The data for the detail table is quite simple and can be calculated.

• How to get the value of the identity column after insertion?
• How can I create rows that should be inserted in a second table? Can I do this in a script?

Do I need to use a Foreach loop at the control flow level, which wraps the parent row in the data flow task, and then has another Foreach loop that inserts detailed records?

Can I just do all the detail row inserts in the script? This would probably be easier than setting up Foreach loops.

Answer 1

Here is one approach.

Create an object type variable.

Create an Execute SQL Task that captures your source data and loads it into a variable (ADO.NET).

Create a container for the ForEach pipeline.

Drag the Success connector (green) from Run SQL Task to ForEach Outline Container. Change the Enumerator in the loop container to "foreach ADO Enumerator" and select your variable from the "ADO object source variable".

In your loop, you should add a “Run SQL Task” that you can work with.

You should be able to use SCOPE_IDENTITY () to get each identifier after inserting it into the main table and use it to insert it into the details table.

Answer 2

I had this problem, on a slightly larger scale (importing deeply nested XML). I was able to use the XML Source function, which creates a surrogate key in the main "table", which 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 lines have been processed, you can use these "foreign keys" to perform any final processing, which requires the simultaneous creation of both main and string lines - even if necessary in a transaction.

This allows you to perform a data flow task in combination with the details specific to your basic information.

Answer 3

One of the methods that I often use when transferring data with an identifier field in the source should have the "OldID" column in the main destination table. Fill it out with SSIS management by entering the original id field in OldID. Subsequent SSIS control then searches the already completed master records, where OldID matches the source identifier and thus receives the newly created identifier.