How Do I Import Repeatable But Non Pre-defined Fields?

hello all,

I'm strugeling for quite soem time and hoping to get your assistance:



facts:

We have a flat file source
each record has the same set of columns, let's call it "identifying columns"
each row can have after the identifying columns (but not all the records have it) a second set of columns, let's call it "detailed info".
the "detailed info" set can repeat itself several time for each record
We have a sql table destination
The row in the table should represent an "indetifying columns" record

sample record 1:

David | 1 | Rodeo Drive | 456 |

sample record 2:

Jeff | 2 |

sample record 3:

John | 3 | Sunny rd | 111 | Marvell str | 6



expected output in the table:

David | 1 | Rodeo Drive | 456

Jeff | 2 | null | null

John | 3 | Sunny rd | 111

John | 3 | Marvell str | 6



question: How do i import the data from the source file into the table, when i dont know in advance how many columns each record has? and how do i divide the sets of "detailed info" into different records in the db?



Thanks,

Eric