title: How to load mixed record type fixed width file with two headers into two separate files
body: <p>I got a task to load a strangely formatted text file. The file contains unwanted data too. It contains two headers back to back and data for each header is specified on alternate lines. Header rows start after <code>------</code>. I need to read both the header along with its corresponding data and dump it into some Excel/table destination using. Let me know how to solve this using any transformation in SSIS or maybe with a script.
  Don't know how to use a script task for this.</p>
  <p>Right now I am reading the file in one column and using a derived column manually trying to split it using <code>substring</code> function. But that works for only one header and it is too hard coded type. I need some dynamic approach to read header rows as well as data rows directly.</p>
  <p>Input file:</p>
  <pre><code>A1234-012                                         I N F O R M A T I C S  C O M P A N Y                                      08/23/17
  PAGE    2 BATCH ABC                                           PAYMENT DATE &amp; DUE DATE                                 EDIT PAGE  481
  ------------------------------------------------------------------------------------------------------------------------------------
   SEO  XRAT CLT     LOAN      OPENING  PAYMENT MATURIUH LOAN NEXE ORIG-AMT   OFF TO CATE  CONTC MON NO.TO  TOL NEL   S CUP CO IND PAT
   NOM  CODE NOM    NOMTER      DATE    DUO DATE  DATE   TIME PT #  MONEY         AQ LOAN  NUMBER    BLOCK   PAYMENT  U TYP GH OMG IND
         1-3 4-6  7-13/90-102  14-19    20-25     26-31 32-34 35-37 38-46   47-48 49 50-51 52-61  62  63      64-72  73 4-5 76 77 8-80
  ------------------------------------------------------------------------------------------------------------------------------------
   SEO  XRAT CLT     LOAN     A/C   A/C     MIN     MAX    MAX   PENDI  LATE CCH  L/F  PARTLYS  CUR   L/F      L/F     L/F
   NOM  CODE NOM    NOMTER    CODE FACTOR   MON     MON    ROAD   DAYS  MONE POT  L/A  L/F JAC  INT  VAD CD  USED PI  VAD DT
         1-3 4-6  7-13/90-102  14  15      20-23   24-29   30-34 35-37   38-42    43     44     49     60     61-63    64-69
  USED-ID:
  ------------------------------------------------------------------------------------------------------------------------------------
  454542 070 567    2136547895 08-08-18 08-06-18 11-02-18 123 256      62,222  LK  5 55 5463218975 5 3       5,555.22    33        H55
  025641 055 123    5144511352 B  .55321   2.55 6531.22                                                 H      #AS
  454542 070 567    2136547895 08-08-18 08-06-18 11-02-18 123 256      62,222  LK  5 55 5463218975 5 3       5,555.22    33        H55
  025641 055 123    5144511352 B  .55321   2.55 6531.22                                                 H      #AS
  454542 070 567    2136547895 08-08-18 08-06-18 11-02-18 123 256      62,222  LK  5 55 5463218975 5 3       5,555.22    33        H55
  025641 055 123    5144511352 B  .55321   2.55 6531.22                                                 H      #AS
  </code></pre>
  <p>Expected output should be:</p>
  <p>FILE 1:</p>
  <pre><code> SEO  XRAT CLT     LOAN      OPENING  PAYMENT MATURIUH LOAN NEXE ORIG-AMT   OFF TO CATE  CONTC MON NO.TO  TOL NEL   S CUP CO IND PAT
   NOM  CODE NOM    NOMTER      DATE    DUO DATE  DATE   TIME PT #  MONEY         AQ LOAN  NUMBER    BLOCK   PAYMENT  U TYP GH OMG IND
  454542 070 567    2136547895 08-08-18 08-06-18 11-02-18 123 256      62,222  LK  5 55 5463218975 5 3       5,555.22    33        H55
  454542 070 567    2136547895 08-08-18 08-06-18 11-02-18 123 256      62,222  LK  5 55 5463218975 5 3       5,555.22    33        H55
  454542 070 567    2136547895 08-08-18 08-06-18 11-02-18 123 256      62,222  LK  5 55 5463218975 5 3       5,555.22    33        H55
  </code></pre>
  <p>FILE 2:</p>
  <pre><code> SEO  XRAT CLT     LOAN     A/C   A/C     MIN     MAX    MAX   PENDI  LATE CCH  L/F  PARTLYS  CUR   L/F      L/F     L/F
   NOM  CODE NOM    NOMTER    CODE FACTOR   MON     MON    ROAD   DAYS  MONE POT  L/A  L/F JAC  INT  VAD CD  USED PI  VAD DT
  025641 055 123    5144511352 B  .55321   2.55 6531.22                                                 H      #AS
  025641 055 123    5144511352 B  .55321   2.55 6531.22                                                 H      #AS
  025641 055 123    5144511352 B  .55321   2.55 6531.22                                                 H      #AS
  </code></pre>
  <p><a href="https://i.stack.imgur.com/QkiaZ.jpg" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/QkiaZ.jpg" alt="Output:File 1 " /></a>
  <a href="https://i.stack.imgur.com/4dpQZ.jpg" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/4dpQZ.jpg" alt="Output:File 2 " /></a>
  <a href="https://i.stack.imgur.com/ag91r.jpg" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/ag91r.jpg" alt="sample data" /></a></p>