Wednesday 12 June 2013

Create flat File Schema using Flat File schema Wizard and put validation for extra fields



This Article describe you how to create flat File Schema using Flat File schema Wizard.
Consider the Sample:

Id|Name|AddressLine1|AddressLine2|City|State
1|Alice Smith|123 Maple Street|Mill Valley|CA|US
2|Robert Smith|8 Oak Avenue|Old Town|PA|US

Right Click on project -> Add -> New Item -> Flat File schema Wizard.
Name your Schema as InputSchema.xsd.



















Select Your Input Instance File, Name your Root Record.
















Select all portions which are used to define the records.


















Select the option for which records are separated .Since records are separated by {CR}{LF} , then select combo box By delimiter symbol .


















Select {CR}{LF} as child delimiter.



















Define Element, Element Type for Records.
Then Select Delimiter for records (|).



















Define Elements Name and Data Type for each element.
Right Click on Schema -> Validate Instance.
It generates xml like this:


<Input xmlns="http://Biztalk.ProcessDelimiterFlatFile.InputSchema">
<Header xmlns="">
  <Id>Id</Id>
  <Name>Name</Name>
  <AddLine1>AddressLine1</AddLine1>
  <AddLine2>AddressLine2</AddLine2>
  <City>City</City>
  <State>State</State>
  </Header>
 <Details xmlns="">
  <Id>1</Id>
  <Name>Alice Smith</Name>
  <AddLine1>123 Maple Street</AddLine1>
  <AddLine2>Mill Valley</AddLine2>
  <City>CA</City>
  <State>US</State>
  </Details>
<Details xmlns="">
  <Id>1</Id>
  <Name>Robert Smith</Name>
  <AddLine1>8 Oak Avenue</AddLine1>
  <AddLine2>Old Town</AddLine2>
  <City>PA</City>
  <State>US</State>
  </Details>
  </Input>


Now Change Instance file by add one extra field
Id|Name|AddressLine1|AddressLine2|City|State
1|Alice Smith|123 Maple Street|Mill Valley|CA|US|US
1|Robert Smith|8 Oak Avenue|Old Town|PA|US

Then Vaidate instance , it will generate xml like this >





<Input xmlns="http://Biztalk.ProcessDelimiterFlatFile.InputSchema">
<Header xmlns="">
  <Id>Id</Id>
  <Name>Name</Name>
  <AddLine1>AddressLine1</AddLine1>
  <AddLine2>AddressLine2</AddLine2>
  <City>City</City>
  <State>State</State>
  </Header>
<Details xmlns="">
  <Id>1</Id>
  <Name>Alice Smith</Name>
  <AddLine1>123 Maple Street</AddLine1>
  <AddLine2>Mill Valley</AddLine2>
  <City>CA</City>
  <State>US|US</State>
  </Details>
<Details xmlns="">
  <Id>1</Id>
  <Name>Robert Smith</Name>
  <AddLine1>8 Oak Avenue</AddLine1>
  <AddLine2>Old Town</AddLine2>
  <City>PA</City>
  <State>US</State>
  </Details>
  </Input>

So this flat file schema is also validating incorrect flat file, to apply such kind of validation
We need to add Patterns at field level.




















Set Pattern as ^ [^|]*.
Now schema will not validate such instance and give error.