I have a CSV file with parents and children in the same row, similar to the structure below:
ParentFirstName, ParentLastName, ParentSSN, Child1FirstName, Child1DOB, Child2FirstName, Child2DOB, ...
I need some expert opinions on the best way to split this flat file into two different tables (data outputs in the Data Flow) in SSIS. The tables I want to create will look similar to this:
Table 1 (Parents):
ParentFirstName, ParentLastName, ParentSSN
Table 2 (Children):
ParentSSN, ChildFirstName, ChildDOB
The actual file has 204 columns with space for 19 children and lots of additional info. Needless to say, I'm looking for the most streamlined and optimized way to accomplish this in SSIS.
Thanks for your help!
Jay