BizTalk FlatFile/CSV Schema

Question

I am refactoring a BizTalk Application. The existing schema has the structure below, representing an invoice, where Level1 is the header, and Level 4 are the multiple line items.

In the old 2010 version of the app, it writes out data one invoice at a time, and has a Send Pipeline using the flat file schema. We are trying to improve old systems and move them to 2013.

I added a Receive Pipeline to read the data back in, and it works fine for a single invoice. But if the file has several invoices in it, it fails.

Can I tweek the schema I already have (vs. having to re-run the wizard) to allow for multiple invoices in the same file? For example, add a "Invoice" record that contains both Level1 and Level4?

[The ultimate goal is to add a Level 0 as well, which includes the total invoices for a the day, and to do that in a map vs a pipeline, as per our architect's preference.]

I tried the following, and get this error:

Reason: Unexpected end of stream while looking for: ','

I used similar steps to BizTalk FlatFile Schema multiple repeating records:

1. Added an Invoice record, and put Level 1 and Level 4 under it.
2. Set the record to unbound
3. Set Child order Infix
4. Child delimiter type to Hex
5. Child delimiter to 0x0D 0x0A

If I try "Child Delimiter" - "None", then I get this error:

Reason: Unexpected data found while looking for: $Delimited$

Then I tried adding the "Tag identifier" (setting to "Level1" and "Level4" respectively). In that case the file with one invoice serialized, but the file with 3 invoices gave a mystery error:

The Messaging Engine encountered an error during the processing of one or more inbound messages.

Sample data:

Level1,,VO,TEST01,12/17/2018,34093092-1,,12/17/2018,60,1075,0,A,,,,12/17/2018,2/15/2019,2/15/2019,,,2000,1000,,1010,1000,,,,,EP
Level4,N,1200,,,1000,INDFRT-ECO,0,,0,145.72,7,34093092-448-887,,,,,,,,,,,,,,,,,
Level4,N,1200,,,1000,INDFRT-ECO,0,,0,802.67,7,34093092-492-930,,,,,,,,,,,,,,,,,
Level4,N,1200,,,1000,INDFRT-ECO,0,,0,126.61,7,34093092-833-096,,,,,,,,,,,,,,,,,
Level1,,VO,TEST01,12/17/2018,34068721-1,,12/17/2018,60,1250,0,A,,,,12/17/2018,2/15/2019,2/15/2019,,,2000,1000,,1010,1000,,,,,EP
Level4,N,1200,,,1000,INDFRT-ECO,0,,0,1250,7,34068721-492-930,,,,,,,,,,,,,,,,,
Level1,,VO,TEST01,12/17/2018,34094975-1,,12/17/2018,60,595,0,A,,,,12/17/2018,2/15/2019,2/15/2019,,,2000,1000,,1010,1000,,,,,EP
Level4,N,1200,,,1000,INDFRT-ECO,0,,0,142.8,7,34094975-448-887,,,,,,,,,,,,,,,,,
Level4,N,1200,,,1000,INDFRT-ECO,0,,0,452.2,7,34094975-492-930,,,,,,,,,,,,,,,,,

Answer 1

Here is a schema that will parse your file.

Note that both the <Sequence> node and the Level4 have Max Occurs unbounded.

The trick when using Tags, is that you either have to include the first delimiter in the tag or to set the delimiter Child Order to Prefix if you don't want an empty field first.

Also if your last line does not end with a CR LF you have to change the Root node (TAR210) Child Order from Postfix to Infix

You will want to either replace the repeating Field with the correct field names, you can do that one field at a time by adding it before the repeating one, or apply this structure to your existing schema.

<?xml version="1.0" encoding="utf-16"?>
<xs:schema xmlns="http://Scratch.SO55026928" xmlns:b="http://schemas.microsoft.com/BizTalk/2003" targetNamespace="http://Scratch.SO55026928" xmlns:xs="http://www.w3.org/2001/XMLSchema">
  <xs:annotation>
    <xs:appinfo>
      <b:schemaInfo standard="Flat File" root_reference="TAR210" default_pad_char=" " pad_char_type="char" count_positions_by_byte="false" parser_optimization="speed" lookahead_depth="3" suppress_empty_nodes="false" generate_empty_nodes="true" allow_early_termination="false" early_terminate_optional_fields="false" allow_message_breakup_of_infix_root="false" compile_parse_tables="false" />
      <schemaEditorExtension:schemaInfo namespaceAlias="b" extensionClass="Microsoft.BizTalk.FlatFileExtension.FlatFileExtension" standardName="Flat File" xmlns:schemaEditorExtension="http://schemas.microsoft.com/BizTalk/2003/SchemaEditorExtensions" />
    </xs:appinfo>
  </xs:annotation>
  <xs:element name="TAR210">
    <xs:annotation>
      <xs:appinfo>
        <b:recordInfo structure="delimited" preserve_delimiter_for_empty_data="true" suppress_trailing_delimiters="false" sequence_number="1" child_order="postfix" child_delimiter_type="hex" child_delimiter="0x0D 0x0A" />
      </xs:appinfo>
    </xs:annotation>
    <xs:complexType>
      <xs:sequence>
        <xs:annotation>
          <xs:appinfo>
            <b:groupInfo sequence_number="0" />
          </xs:appinfo>
        </xs:annotation>
        <xs:sequence maxOccurs="unbounded">
          <xs:annotation>
            <xs:appinfo>
              <b:groupInfo sequence_number="1" />
            </xs:appinfo>
          </xs:annotation>
          <xs:element name="Level1">
            <xs:annotation>
              <xs:appinfo>
                <b:recordInfo structure="delimited" preserve_delimiter_for_empty_data="true" suppress_trailing_delimiters="false" repeating_delimiter_type="char" repeating_delimiter="," sequence_number="1" tag_name="Level1" child_order="prefix" />
              </xs:appinfo>
            </xs:annotation>
            <xs:complexType>
              <xs:sequence>
                <xs:annotation>
                  <xs:appinfo>
                    <b:groupInfo sequence_number="0" />
                  </xs:appinfo>
                </xs:annotation>
                <xs:element maxOccurs="unbounded" name="Field" type="xs:string">
                  <xs:annotation>
                    <xs:appinfo>
                      <b:fieldInfo justification="left" sequence_number="1" />
                    </xs:appinfo>
                  </xs:annotation>
                </xs:element>
              </xs:sequence>
            </xs:complexType>
          </xs:element>
          <xs:element maxOccurs="unbounded" name="Level4">
            <xs:annotation>
              <xs:appinfo>
                <b:recordInfo sequence_number="2" structure="delimited" preserve_delimiter_for_empty_data="true" suppress_trailing_delimiters="false" repeating_delimiter_type="char" repeating_delimiter="," tag_name="Level4" child_order="prefix" />
              </xs:appinfo>
            </xs:annotation>
            <xs:complexType>
              <xs:sequence>
                <xs:annotation>
                  <xs:appinfo>
                    <b:groupInfo sequence_number="0" />
                  </xs:appinfo>
                </xs:annotation>
                <xs:element maxOccurs="unbounded" name="Field" type="xs:string">
                  <xs:annotation>
                    <xs:appinfo>
                      <b:fieldInfo justification="left" sequence_number="1" />
                    </xs:appinfo>
                  </xs:annotation>
                </xs:element>
              </xs:sequence>
            </xs:complexType>
          </xs:element>
        </xs:sequence>
      </xs:sequence>
    </xs:complexType>
  </xs:element>
</xs:schema>