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
BizTalk Flat File Parsing Annotations new!
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