SSIS data migration: split flat table into parent + child / grandchild tables
I need to migrate data from a large flat table located in SQL Server 2005 to a new SQL Server 2005 schema that consists of a parent table and multiple child tables. It looks like the opposite merge or merge in SSIS, but I don't understand how I would go about it. Any guidance is appreciated. Ever seen examples of how others achieve this kind of thing?
The flat source table [FlatSource] has <280K and some garbage data, so I will need to deal with these things at some point. But for now, here's the gist of what I need to accomplish ...
The flat source table will basically map to the new parent table [Parent]. That is: for every record in [FlatSource] I need to move that record to [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 to the child table [Child1]. Basically, there could be 0-4 columns which, if filled, would require a new record in [Child1] that would use the PK from [Parent].
Once this is done, I will need to fill in 0-4 new entries in [Grandchild] that will use the PK from [Child].
Thanks for any insight you can offer. I started a project in C #, but the more I delve into it, the more it looks like a task for SSIS.
Respectfully,
Josh Blair Golden, CO
source to share
It looks like this would be a task for a "conditional splt" dataflow task. This will sit after your datasource and you would add various splt clauses inside the component itself.
When connecting conditional split receivers, you can specify which "condition" the receiver receives. Since you can have many conditions, you can have many directions.
source to share