Biztalk Flat File Transform multiple unbound records causing Unexpected Data Error

Question

I am trying to import a flat file into a SQL Database via Biztalk 2006 R2. The input file has a layout as follows with each line separated by CR/LF with an extra trailing CR/LF at the end:

00(29characters after)  <=== Header
07(997characters after) <=== Record Type 07 (unbounded, 0-?? possible records)
08(86characters after)  <=== Record Type 08 (unbounded, 0-?? possible records)
09(89characters after)  <=== Record Type 09 (unbounded, 0-?? possible records)
10(94characters after)  <=== Record Type 10 (unbounded, 0-?? possible records)
16(35characters after)  <=== Group Footer
17(30characters after)  <=== File Footer

Anyway, i ran the Flat File Wizard, and it created an XML, but even with "Repeating Records" selected, it set the min and max occurrences of 07,08,09 and 10. I changed the min to 0 and the max to unbounded. Now, no matter what i do, i get the Unexpected data found while looking for:'\r\n' error when validating. I've tried setting the Default Child Order to Postfix and the Child Order of the root to Infix and Postfix both. Nothing seems to help.

Answer 1

Creating the Schema from Scratch

I think using the Flat File Schema Wizard even for moderately complex structures, like this one, is not worth the trouble. My suggestion, is to think about the overall structure, and provide an outline using the Schema Editor.

So, you example calls for a schema that has the following structure :

A single Header record typed 00, followed by a sequence of records typed 07, 08, 09and 10 respectively. Each typed record is a structure in and of itself, which contains any number of repeating records. Finally, the structure ends with a single GroupTrailer record typed 16, followed by an overall Trailerrecord, typed 17.

This maps nicely to the following schema in BizTalk :

Now, you need to tweak various properties of the nodes in order to instruct the Flat File Disassembler how to parse your incoming messages.

Root Record

The Root record is just there to group the various child records together and is required for a properly structured XML document. However, it does not participate in the parsing of the incoming structure.

Therefore, you should set the Child Delimiter Type to None.

Header, GroupTrailer and Trailer Records

The Header, GroupTrailer and Trailer records each occur a single time, so leave their Min Occurs and Max Occurs properties to their default value of 1.

Furthermore, each of these records are identified by an appropriate Tag Identifier of 00, 16 and 17 respectively.

Finally, those records each end with a trailing CR/LF pair or characters. Therefore, set their Child Delimiter Type properties to Hexadecimal, and their Child Order properties to Postfix.

Type07, Type08, Type09 and Type10 Structures

This is the tricky part.

One way to look at those structures is that they contain repeating records, each delimited with a trailing CR/LF. However, the structures themselves appear only once.

Another important point is that you only need a single CR/LF pair as a delimiter for both the structures and their child records. So, the settings of the Child Delimiter Type properties should reflect that.

For the Type07, Type08, Type09 and Type10 records, leave the default settings. That is, set the Child Delimiter Type property to None and the Child Order property to Conditional Default. In particular, there is no Tag Identifier set for these records.

Type07_Record, Type08_Record, Type09_Record and Type10_Record Structures

However, the Type07_Record, Type08_Record, Type09_Record and Type10_Record are set to occur multiple times. Set their Min Occurs properties to 0 and Max Occurs properties to unbounded.

Additionnaly, each repeating record ends with a trailing CR/LF pair. Therefore, set their Child Delimiter Type properties to Hexadecimal, their Child Order properties to Postfix and their Child Delimiter properties to 0x0D 0x0A.

Reference

For reference, the resulting settings are :

• Root : Delimited, None, Conditional Default.

• Header : Delimited, Hexadecimal, 0x0D 0x0A, Postfix, Tag Identifier 00.

• <Sequence> : (optional), MinOccurs: 1, MaxOccurs: 1

• Type07 : Delimited, None, Conditional Default.

• Type07_Record : Delimited, Hexadecimal, 0x0D 0x0A, Postfix, Tag Identifier 07.

• Type08 : Delimited, None, Conditional Default.

• Type08_Record : Delimited, Hexadecimal, 0x0D 0x0A, Postfix, Tag Identifier 08.

• Type09 : Delimited, None, Conditional Default.

• Type09_Record : Delimited, Hexadecimal, 0x0D 0x0A, Postfix, Tag Identifier 09.

• Type10 : Delimited, None, Conditional Default.

• Type10_Record : Delimited, Hexadecimal, 0x0D 0x0A, Postfix, Tag Identifier 10.

• GroupTrailer : Delimited, Hexadecimal, 0x0D 0x0A, Postfix, Tag Identifier 16.

• Trailer : Delimited, Hexadecimal, 0x0D 0x0A, Postfix, Tag Identifier 17.

Answer 2

There are a couple ways to fix this. First, there is a property on your record-level schema node that allows you to ignore/suppress trailing delimiters. However, you can run into issues if that extra /r/n is not consistently coming across I. Your file.

The second option is to add an extra record node that has no columns and is min=0 and max=1. I can get you more specifics if you need to go this way.