Geeks With Blogs

BizTalk Blog by Chris Han System Design for Enterprise Agility,

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 !

You can download the video here, I’ll show you how to model the xml schema for this flat file by using BizTalk 2006 Flat File Wizard.

http://www.urooj.net/archive/ffw in action.zip

Posted on Friday, February 10, 2006 11:37 AM BizTalk | Back to top


Comments on this post: BizTalk 2006 Flat File Wizard: How to model a complicated flat file schema

# re: BizTalk 2006 Flat File Wizard: How to model a complicated flat file schema
Requesting Gravatar...
Chris,

Did you ever get around to your "next blog" where you explain how to import a complex flat file like this one via the BizTalk Flat File Wizard?

-Krip
Left by Anil Kripalani on Apr 05, 2006 12:56 PM

# re: BizTalk 2006 Flat File Wizard: How to model a complicated flat file schema
Requesting Gravatar...
Anil,

I'll upload a 7MB video walk through in a day or two.

I appologize for the delay.
Chris
Left by Chris Han on Apr 05, 2006 1:09 PM

# re: BizTalk 2006 Flat File Wizard: How to model a complicated flat file schema
Requesting Gravatar...
Hi

I need a similar kind of schema, Where can I find the video. Please let me know
Left by Sunil on Apr 17, 2006 7:22 AM

# re: BizTalk 2006 Flat File Wizard: How to model a complicated flat file schema
Requesting Gravatar...
This video is better view on Windows 2003. The information provided is for educational purpose only and as is.

http://www.urooj.net/archive/ffw in action.zip
Left by Chris Han on Apr 18, 2006 7:49 PM

# re: BizTalk 2006 Flat File Wizard: How to model a complicated flat file schema
Requesting Gravatar...
Chris, is this vid still available, the link you posted no longer works?
Left by George K on Aug 15, 2006 6:34 AM

# re: BizTalk 2006 Flat File Wizard: How to model a complicated flat file schema
Requesting Gravatar...
Yes, it's still on, but make sure you copy the whole url to a browser in stead of just click on the blue link.
Left by chris han on Aug 15, 2006 6:55 AM

# re: BizTalk 2006 Flat File Wizard: How to model a complicated flat file schema
Requesting Gravatar...
I want to make the schema of this file in wizard .

UNA:+.? '
UNB+UNOC:3+Jade BV:14+SS/DBLI/HKLI/F/WS/V/S:14+050913:1456+EDI01014'
UNH+130001009N+PRICAT:D:93A:UN:EANNL2'
BGM+9:::VER200+Catalogus Jade+2'
DTM+137:20050913:102'
DTM+157:20050913:102'
DTM+21E:20060531:102'
FTX+CAT+1++Jade'
RFF+CH:Catalogus Jade::9'
NAD+SU+8713552000009::9'
CUX+2:EUR:4'
CUX+2:EUR:10'
PGI+3'
NAD+SU+8713552000009::9'
PIT+1+1'
PIA+5+8713552003680:EN'
PIA+1+8550140:SA'
PIA+1+12733:GU'
IMD+F+ANM+:::DEKBED LIBELLE SYNT.4S 140X200'
IMD+F+TPE+:::Dekbedden'
IMD+F+DSC+:::Libelle Dekbedden'
MEA+PD+LN+CMT:200'
MEA+PD+WD+CMT:140'
DTM+157:20050427:102'
DTM+21E:20060531:102'
TAX+7+VAT+++:::19+S'
PRI+XXX:129.9'
PAC+++'
HAN+STK::9'



can u help me in this regard?
Thanks
Hemant
Left by Hemant on Jan 24, 2007 2:38 AM

# Can you just Send me the file
Requesting Gravatar...
I downloaded the zip, but saying it's corrupted.

Can you just send me the file.

Thanks,

Jason Stott
Left by Jason Stott on Jun 01, 2007 5:08 PM

# re: BizTalk 2006 Flat File Wizard: How to model a complicated flat file schema
Requesting Gravatar...
Hi,

can any one please let me know some online biztalk resource to learn.


thanks in advance.
Left by Genious on Jul 20, 2007 10:52 AM

# re: BizTalk 2006 Flat File Wizard: How to model a complicated flat file schema
Requesting Gravatar...
here: http://www.microsoft.com/biztalk/techinfo/virtual-labs.mspx
Left by chris on Jul 21, 2007 5:44 PM

# re: BizTalk 2006 Flat File Wizard: How to model a complicated flat file schema
Requesting Gravatar...
Jason,

Some thoughts:
Make sure Type0 is returning 'empty' or not got mapped at all.

If it is returning 'empty', it is possiblely type casing problem. Try to feed a data file with only Type0 values.

That's first step to isolate the problem I can think of. Let me know.
Left by chris on Feb 09, 2008 10:10 AM

# re: BizTalk 2006 Flat File Wizard: How to model a complicated flat file schema
Requesting Gravatar...
hiya, i can't acess the video, can you email me the same
Left by Prasanna krishnan on Feb 20, 2010 10:17 AM

# re: BizTalk 2006 Flat File Wizard: How to model a complicated flat file schema
Requesting Gravatar...
I also cannot access the video, and the latest microsoft link you provided does not have a complicated example like yours. Can you please email me the video or a current link to it?
Thanks,
Dan
Left by Dan on Mar 28, 2010 8:42 PM

Your comment:
 (will show your gravatar)


Copyright © Chris Han | Powered by: GeeksWithBlogs.net