BizTalk Blog by Chris Han

I'm recently working on a BizTalk project for a financial industrial ASP. The BizTalk 2006 is used to exchange data with the partners.

As the legacy of 2 decades, all the data being exchanged are in flat file format. BizTalk 2006 Flat File Wizard is very handy when modeling the xml schema off the flat files. In most of the cases, it's just several clicks. The best part of this Flat File Wizard is to give you a visually design experience.

Flat File Wizard is great, it helps a lot in simplifying the trivial jobs. But it's by no mean Harry Potter's magic wand. In fact like all other higher level tools, it trades off flexibility for simplicity. You do have to understand XML and the old dirty way of doing flat file modeling when facing a complex file.

One of the challenges I'm facing is files from Merrill Lynch. It contains a header line at the beginning and a trailer line at the end. Both header and trailer lines has a tag identifier at the beginning of the line. Between header and trailer lines, there are multiple data lines. Each of them belongs to a certain type indicated by a tag identifier at position 8. Header, trailer, and each data line in between are delimited by a line feed symbol “\n”. See the example below:

HEADER RECORD ACI 20050114 200501150459 28B00143                                                   

11510N43B115PARIS CEDEX 16        112 AVENUE KLEBER             BP 2002 16                         

11510N430CC FICMA            54                                                                     

12245N43B115Jersey City 08        201 AVENUE addf45             lm 2006 44                         

11510N43CPA 75761    FRANCE              33-15-365-5756                                            

14518H31P00661009158106APD      000000000001801SN20020408000000004993064+00000000576900400000000008

22222H31P12121229158106APD      000000000001801SN20020408000000004993064-00000000576900400000000008

14518H31MN28B00145NUVEEN/RITT ASSET MGMT. BAL                                                      

22222H31MM19560011Modle X Block K                                                                  

33333H31MM22222022Modle X Block D                                                                  

11510N43NN1MR PASPASCAL                      DETEMMERMAN                                            

67688N43NL1FirstName                     SecondName                                                

11510N43AL2HEWLETT-PACKARD               ROUTE DU NANT-D'AVRIL 150                                 

11510N43RLEER34434-4232434               ROUTE DU NANT-D'AVRIL 150                                 

11510N43X34EEEEEER3333KARD               ROUTE DU NANT-D'AVRIL 150                                 

TRAILER RECORD 000011824  000000170                                                                 

The challenges here are:

1.  The tag identifier for each data line is not at the position 0 but 8.
2. Each type of data line can appear arbitrary number of time and
3. in arbitrary order !