Biztalk Flat File Transform multiple unbound records causing Unexpected Data Error -

i trying import flat file sql database via biztalk 2006 r2. input file has layout follows each line separated cr/lf trailing cr/lf @ 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, ran flat file wizard, , created xml, "repeating records" selected, set min , max occurrences of 07,08,09 , 10. changed min 0 , max unbounded. now, no matter do, unexpected data found while looking for:'\r\n' error when validating. i've tried setting default child order postfix , child order of root infix , postfix both. nothing seems help.

creating schema scratch

i think using flat file schema wizard moderately complex structures, one, not worth trouble. suggestion, think overall structure, , provide outline using schema editor.

so, example calls schema has following structure :

a single header record typed 00, followed sequence of records typed 07, 08, 09and 10 respectively. each typed record structure in , of itself, contains number of repeating records. finally, structure ends single grouptrailer record typed 16, followed overall trailerrecord, typed 17.

this maps nicely following schema in biztalk :

now, need tweak various properties of nodes in order instruct flat file disassembler how parse incoming messages.

root record

the root record there group various child records , required structured xml document. however, not participate in parsing of incoming structure.

therefore, should set child delimiter type none.

header, grouptrailer , trailer records

the header, grouptrailer , trailer records each occur single time, leave min occurs , max occurs properties default value of 1.

furthermore, each of these records identified appropriate tag identifier of 00, 16 , 17 respectively.

finally, records each end trailing cr/lf pair or characters. therefore, set child delimiter type properties hexadecimal, , child order properties postfix.

type07, type08, type09 , type10 structures

this tricky part.

one way @ structures contain repeating records, each delimited trailing cr/lf. however, structures appear once.

another important point need single cr/lf pair delimiter both structures , child records. so, settings of child delimiter type properties should reflect that.

for type07, type08, type09 , type10 records, leave default settings. is, set child delimiter type property none , child order property conditional default. in particular, there no tag identifier set these records.

type07_record, type08_record, type09_record , type10_record structures

however, type07_record, type08_record, type09_record , type10_record set occur multiple times. set min occurs properties 0 , max occurs properties unbounded.

additionnaly, each repeating record ends trailing cr/lf pair. therefore, set child delimiter type properties hexadecimal, child order properties postfix , child delimiter properties 0x0d 0x0a.

reference

for reference, resulting settings :

• 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.