Results 1 to 4 of 4
  1. #1
    Join Date
    Dec 2003
    Posts
    8

    Unhappy 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):

    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!!!

  2. #2
    Join Date
    Dec 2003
    Posts
    8

    Correction!

    It should be:

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

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

  3. #3
    Join Date
    Feb 2004
    Location
    Hamburg, Germany
    Posts
    22
    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.

  4. #4
    Join Date
    Dec 2002
    Posts
    1,245
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •