DTS load master file to different tables

We need to load a "master" flat file to SQL Server tables. The file is a dump from mainframe. Based on a field called "record_type", each record in the file has different columns. I would use the following as an example (the real file is much more complicated than this, but you get the idea):

For instance, my file has:

20M02221984PAPepsi1000
23F11121987MD1000
01M09182003TXCocacola1100
34F03041970DC900

If "M", the fields are "age", "gender", "birthdate", "state", "salary"

If "F", the fields are "age", "gender", "birthdate", "state", "company", "salary"

We need to load the file (only one file) into two different tables, M_table, and F_table. But I have researched and discovered in DTS the source (TEXT file) can not be queried against to filter on the gender field.

Since each record may have different number of fields, I cannot really load the flat file into a "staging" table.

Does anyone has any idea on how to achieve this? Thanks in advance!!!It should be:

If "F", the fields are "age", "gender", "birthdate", "state", "salary"

If "M", the fields are "age", "gender", "birthdate", "state", "company", "salary"|||Since your data sets are obviously not fixed-length, you could use a temporary master table into which you import all data sets, e.g. into only one column of varchar(nnn).

Then use queries with string-functions to split the data into the correct number and type of columns for the respective tables M_table and F_table.|||kbk's solution is probably simpler (and therefore usually better). You may also consider a Data Driven Query task. It won't be entirely straightforward and it will require a substantial amount of work in the ActiveX script component (and it's performance will be slower).

But other than these drawbacks, it may work!

Regards,

hmscott