SSIS Data Migration: Split Flat table into Parent + Child/Grandchild tables

Question

Hello,

I need to migrate data in a large flat table located in SQL Server 2005 into a new SQL Server 2005 schema that consists of a parent table and multiple child tables. This seems like the opposite of a merge or merge join in SSIS but I don't understand how I would go about accomplishing this. Any recommendations are greatly appreciated. Ever seen any examples of how other accomplish this sort of thing?

The flat Source table [FlatSource] has < 280K records and some garbage data so I will need to handle these things at some point. But for now, here is gist of what I need to accomplish...

The flat source table will mostly map to the new parent table [Parent]. That is to say: For each record in the [FlatSource], I need to move this record into [Parent].

Once this is done, I need to record the PK of this new parent record and add numerous child records. This PK will be used when adding 0-4 records into a child table [Child1]. Basically there may be 0-4 columns that if populated will require a new record in [Child1] that will use the PK from the [Parent].

Once this is done, I will need to populate 0-4 new records into [Grandchild] that will use the PK from [Child].

Thanks for any insight you can offer. I have started a project in C# but the more I dig into it, the more it seems like a task for SSIS.

Sincerely,

Josh Blair Golden, CO

Answer 1

It looks like this would have been the task for a 'conditional splt' data flow task. This would have sat after your data source, and you would have added different splt conditions within the component itself.

When connecting destinations the the conditional split, you can specify which 'condition' is being recieved by the destination. As you can have many conditions, you can have many destinations.