How to insert the records in child table when records are inserted into parents table in SSIS?

Question

I am stuck with a scenario in SSIS. I have two table both maintain the referential integrity means one is parent table and another is child. When I insert the some records in parent table then those new records should be insert into child table.

Please share if you have any idea to implement this in SSIS.

Answer 1

From your description I take for granted, that you know WHICH child records have to be cretaed for WHICH master record, so I guess there are basiacally two possibilities.

1. All data is known at runtime:
   • Create a dataflow, which populates the master table
   • Cretae a second dataflow, which populates the child table and connect it to the first dataflow via Succeed constraint

But basically this sounds bit too easy - guess you tried this out already? So here comes possibility 2: the parent table creates IDs, which have to be referenced by the child records:

• Again we start with one dataflow in order to populate the master table
• then we add a second dataflow - again attached to the first one via succeed constraint
• in this dataflow we add a lookup, which checks the master table for the corresponding IDs
• This ID is then written to the corresponding column in the child table.