Unanswered: 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):
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!!!
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).