Managing Flat Files in BizTalk 2004

by | Apr 21, 2005 | BizTalk Community Blogs via Syndication | 0 comments


I have done a heap of work with Flat Files (mostly csv) in Biztalk 2004 I collected a lot of articles from other Blogs on this which were fab I though I would centralise this info for you, again thanks goes to the fellow bloggers for posting this stuff.


BizTalk 2004 Flat File Schema Tutorial 1


BizTalk 2004 Flat File Schema Tutorial 2


BizTalk Flat File Parsing Annotations


The flat file strikes back: BizTalk 2004 parsing positional records


Not sure where I got this from but it’s a great way of removing the first line from a file if it contains the column headers without having to write a new biztalk dissassembler pipeline component, so thanks to whoever worked this one out.


“To setup a CSV / TSV file, do the following
Setup a schedma as normal. It should be -> products -> product -> all your fields
In , select schema editor extensions, and add “Flat File Extension”
In Products, set the child order to postfix, the child delimiter type to “Hex” and the delimiter to “0x0D 0x0A” (CR/LF). Adjust as needed.
In Product, set the Child order to Infix, the type to Hex (or char) and the delimiter to 0x09 (tab), or , (comma). Adjust as needed.
To skip the first line of a file, during the mapping (eg, the file has a header).
Setup the mapping as normal
Add an Iteration functiod. Connect it up to the “product” level record (eg, product, not products).
Add a not equal (<>) functiod. Connect it to the Iteration functoid, and the target record. Set the second value on that to 1.
If you have trouble deploying a generated xml deployment file (eg, from BTSDeploy wizard) which contains a sql connector, you may find that the user that BizTalkServer is using, does not have access to the target database. Give it access :).
Send Ports are the best place to put maps. I never managed to get them to work in receive ports – the documents tended to disappear.
When in doubt, reboot. For example, if everything works on the development machine, but for some odd reason deploy on the test server and then doesn’t work – reboot the test server. Chances are, its managed to have something hang around in the GAC or something…. restarting BTS might work too.”


This next one is a good overview of property setting on a flat file schema it is taken from this blog post which is not accessable at the moment: http://weblogs.ilg.com/brumfieldb/archive/2004/08/09/440.aspx


There doesn’t seem to be a lot of good information on disassembling various types of flat-files in BizTalk.   There are a number of flat-file properties added for a schema set with the Flat-File Extensions, but it’s not always clear on how to use all these options to accomplish what you need.  Hopefully this post will help serve as an example of how to use some of the flat-file features in BizTalk to parse more than a straightforward comma delimited file.


For one of my projects, I needed to disassemble a file with a format like this:






HEADER    USER_1234

PURCHASE    PO_001

LINEITEM    Item32    33

LINEITEM    Item63    45

PURCHASE    PO_002

LINEITEM    Item454    12

The file contains a header record for each file, one or more purchase orders each with one or more line items.  Each line contains a tag that indicates its usage (e.g. LINEITEM).  The elements for each line are separated by a tab (ASCII hex code of 0x09).


We define a target schema to represent the xml-ized version of this file.


The schema root and each element group must be appropriately configured to correctly parse the flat file.  The configuration for each root and group node are defined as follows:



Schema Root

















Property Setting
Default Child Delimiter 0x0D 0x0A
Default Child Delimiter Type Hexadecimal
Default Child Order Postfix


The settings in the schema root define the default usage in sub-groups throughout the schema.  Here we’ve defined it to be CR LF and the Default Child Order defines that the data will precede the delimiters.  These settings can be overridden at each group level if necessary.



Orders















Property Setting
Child Delimiter Type None
Child Order Postfix


Header



























Property Setting
Child Delimiter 0x09
Child Delimiter Type Hexadecimal
Tag Identifier HEADER
Child Order Prefix
Min Occurs 1
Max Occurs 1


The header group allows us to specify a tag identifier as well as note that the elements on this line are tab (0x09) delimited.


Order





















Property Setting
Child Delimiter Type None
Child Order Postfix
Min Occurs 1
Max Occurs unbounded


OrderHeader





















Property Setting
Child Delimiter 0x09
Child Delimiter Type Hexadecimal
Tag Identifier PURCHASE
Child Order Prefix


LineItems















Property Setting
Child Delimiter Type Default Child Delimiter
Child Order Postfix


LineItems is a logical grouping of LineItems.  Since LineItems in the flat file are separated by a CR LF, the Delimiter type is set to the default child delimiter and that the delimiter will appear after the child item.


LineItem



























Property Setting
Child Delimiter 0x09
Child Delimiter Type Hexadecimal
Tag Identifier LINEITEM
Child Order Prefix
Min Occurs 1
Max Occurs unbounded


Notes: 



  • Most of the group nodes do not have any real values in the flat-file, but instead are logical groupings of real elements.  For instance, LineItems has no real representation in the flat file, but is the logical grouping of LineItem elements from the flat file.  These groups must be setup to for the postfix child order.
  • Tag Identifiers are used by BTS to recognize a line, but are not imported as data.
  • With this specification, there needs to be a CR LF following the last line.

R. Addis

Submit a Comment