How to ignore a line after a non-repeating record in a flat file using the Flat File Schema wizard?

Question

Hello.

I have a flat file that has a redundant Carriage Return after the last data line, like this.

HEADER|data|data|data|data|data|[CR][LF]
BODY|data|data|data|data|data|data|data|data|data|data|data|data|data|data|data|[CR][LF]
BODY|data|data|data|data|data|data|data|data|data|data|data|data|data|data|data|[CR][LF]
BODY|data|data|data|data|data|data|data|data|data|data|data|data|data|data|data|[CR][LF]
FOOTER|data|data|data|data|data|[CR][LF]
[CR][LF]

The FOOTER row will not repeat.

While designing the Child Elements, I can't set the Element Type = IGNORE for the last empty row, because the Wizard expects me to set FOOTER's Element Type = Repeating Record, which is not true.

If I ignore the last line while importing the Flat file by not highlighting it, all my input files fail with the error:

"The remaining stream has unrecognizable data."

Please help

---

dministrator.blogspot.com

Answer 1

Set "Allow Early Termination" property of Schema node to "Yes". The reason it should work is actually a side effect. The main purpose of this property is easy to guess: allow early termination of the last positional field in the record. It actually should be named "Allow Early/Late Termination" :)

Answer 2

The side effect actually is quite nasty. Use it cautiously. The reason is that it ignores not only CRLFs that follow your footer but actually ANY DATA. 

So, if you pass a file like this:

HEADER|data|data|data|data|data|[CR][LF]
BODY|data|data|data|data|data|data|data|data|data|data|data|data|data|data|data|[CR][LF]
BODY|data|data|data|data|data|data|data|data|data|data|data|data|data|data|data|[CR][LF]
FOOTER|data|data|data|data|data|[CR][LF]
[CR][LF]
RUBBISH RUBBISH RUBBISH

It will be validated and the rubbish will be ignored. This might not be what you want because the "rubbish" might actually be a valid data (mis-formed by another system).

By the way, files which look like this (i.e. 2 messages concatenated in 1 file):

 

HEADER|data|data|data|data|data|[CR][LF]
BODY|data|data|data|data|data|data|data|data|data|data|data|data|data|data|data|[CR][LF]
BODY|data|data|data|data|data|data|data|data|data|data|data|data|data|data|data|[CR][LF]
FOOTER|data|data|data|data|data|[CR][LF]HEADER|data|data|data|data|data|[CR][LF]
HEADER|data|data|data|data|data|[CR][LF]
BODY|data|data|data|data|data|data|data|data|data|data|data|data|data|data|data|[CR][LF]
FOOTER|data|data|data|data|data|[CR][LF]

will be validated and split into 2 interchanges (biztalk messages), so you would not lose the 2nd message but it might break your system if it's not prepared to cope with this.

Answer 3

If all of this does not work for you can have a separate trailer schema which would catch and swallow only CRLFs (set it in the FFDasm properties as trailer schema):

<?xml version="1.0" encoding="utf-16"?>
<xs:schema xmlns:b="http://schemas.microsoft.com/BizTalk/2003" xmlns:tr="http://Test.Schemas.SwallowCRLF" targetNamespace="http://Test.Schemas.SwallowCRLF" xmlns:xs="http://www.w3.org/2001/XMLSchema">
  <xs:simpleType name="CRLFOnly">
    <xs:restriction base="xs:string">
      <xs:pattern value="[\r\n]*" />
    </xs:restriction>
  </xs:simpleType>
  <xs:element name="Root">
    <xs:complexType>
      <xs:sequence minOccurs="0">
        <xs:element minOccurs="0" name="CRLFElement" type="tr:CRLFOnly" />
      </xs:sequence>
    </xs:complexType>
  </xs:element>
</xs:schema>

Answer 4

Hello. In my case, each Flat file will only contain only one header and only one footer with multiple bodies. If it contains junk data or even valid data after the redundant [CR][LF] at the bottom, too bad, it will be ignored by our BT setup because it is violation of agreed upon schemas. :-)

I will try both your Allow Early Termination and the FFDasm schema suggestions. I will report here which one worked for me. In the meantime, if you come across any other way, please share.


I'm using BTS2006 R2+VS2005, but do they have any enhancements in BTS2009+VS2008SP1 to address this case?

THank you again for taking time to post. I learnt something new from each of your three responses.

---

dministrator.blogspot.com

Answer 5

Hi,

I had a similar situation a while ago. I had a flat file coming in with multiple lines.

I didn't really have a header or trailer, just the lines.

You have to set the "child_order" property to "postfix" I think. (it's been a while)

When you still face issues, adding an extra level in your schema could help.

That's what did the trick for me.

Kind Regards

Tim

Answer 6

Thank you for all the responses. I used Kiryl's idea, but modified the trailer schema as follows. It works. Thank you.

<?xml version="1.0" encoding="utf-16"?>
<xs:schema xmlns:b="http://schemas.microsoft.com/BizTalk/2003" xmlns="http://MyProject.Schema" targetNamespace="http://MyProject.Schema" xmlns:xs="http://www.w3.org/2001/XMLSchema">
  <xs:element name="Root">
    <xs:complexType>
      <xs:sequence>
        <xs:element minOccurs="0" name="Element1" nillable="true" type="xs:string" />
      </xs:sequence>
    </xs:complexType>
  </xs:element>
</xs:schema>

---

-Perennial Newbie-