BizTalk Server: Teach me something new about Flat Files (or not) – Positional Files

In the second post of this series, we follow a simple walkthrough explaining the basic principles to create a flat-file schema from a file delimited by symbols. Now it’s time to do the same thing, but this time translating an inbound positional flat file.

A positional flat file it’s normally a file that has fields that have a fixed maximum length and records that have a common end-of-record terminator, for example:

Sandro      Pereira         {CR}{LF}
José        Silva           {CR}{LF}

Where the first fixed 12 characters describe the Name field and the following 16 characters describe the Surname field. White space padding the end of each field and the Carriage Return/Line Feed characters is the common end-of-record terminator.

The following walkthrough shows you how to create a flat-file schema from a positional flat file document that stores employee information using the BizTalk Flat File Schema Wizard.

Field DescriptionLength
GroupId4
PrimaryKey2
SecondaryKey2
ControlFlag1
PartyNo1
Remark3
Country10
Name12
Surname16
Street22
City12
District19
ZipCode

File sample:

0120200300 01 PT        Sandro      Pereira         Rua Central, 133      Crestuma    Vila Nova de Gaia  4415 Crestuma
0120200300 01 PT        José        Silva           Crestuna              Crestuma    Vila Nova de Gaia  4415 Crestuma
0120200300 01 PT        Rui         Barbosa         Porto                 Porto       Porto              4400 Crestuma
0120200300 01 PT        Miguel      Silva                                 Porto       Porto              4415 Crestuma

Once again I will walk you through the required steps at design time:

  • using the Flat File Schema Wizard to generate the Flat File Schema;
  • and the pipeline editor to create a custom pipeline;

To enable the runtime to support the processing of the Flat File described earlier (Employees)

We begin then by launching Visual Studio and create or open a BizTalk project:

  • “File –> New –> Project”, on BizTalk Projects, select the option “Empty BizTalk Server Project”;
  • Insert the project name, physical location on the disk and the name of the solution.

Using Flat File Schema Wizard to generate a schema based on a positional flat file

To create the schema which will recognize the ‘Employees’ text file, we need to go to the BizTalk solution created in Visual Studio and perform the following steps:

  • Press the right button on top of the project in Solution Explorer, and select the option “Add –> New Item…
  • On “Installed Templates” menu in the window “Add New Item”, select the option “Schema Files”, and then select the option “Flat File Schema Wizard”, then provide the name you want to give the scheme in this example: “FF_Positional_Employees.xsd”

Once again, as we explain previous, by selecting this option, we will be guided automatically by the tool “BizTalk Flat File Schema Wizard” that will help us to create a Flat File Schema and define its data structure (records, elements, attributes …) based on the text file specified.

  • Select “Next” to continue.
 BizTalk Flat-File Schema Wizard Welcome Page positional
  • In the window “Flat File Schema Information” we will have to:
    • Select an instance of the text file that will serve as the model of the structure that we want to transform;
    • Although it is not necessary, it is good practice to rename the Record name “Root”.
      • In this case, we will rename it to “Employees”
    • And finally, assign a “Target namespace” to the scheme and define the encoding of the input file
 BizTalk Flat-File Schema Wizard information positional

Note: The wizard will load the text file so that we can begin to split it and map it into the desired structure. In this step, we need to define how the records or rows are differentiated.

  • Since each record “Employee” that we want to create is defined in a single line, in the “Select Document Data” we can select only the data portion of the document that will set the record, i.e. the whole first line.
BizTalk Flat-File Schema Wizard Document Data positional

Note: Alternatively, let’s call it: option 2, we could select all the data of the file, however with this approach we need to treat differently the data a few steps ahead in the “Child Elements” window.

BizTalk Flat-File Schema Wizard Document Data positional

Note: if you notice that some characters are not being parsed correctly as the picture below shows, then you probably need to go back to the “Flat File Schema Information” windows and set a different code page, for example, “Western-European (1252)” instead of “UTF-8 (65001)”.

BizTalk Flat-File Schema Wizard Document Data positional
  • In the window “Select Record Format”, even though we are dealing with a positional flat file, at this point we are trying to define the structure of each “Employee” record which is delimited by a return Carriage Return/Line Feed ({CR}{LF}). So, in this case, we will have to select the “By delimiter symbol” option.
BizTalk Flat-File Schema Wizard Record Format positional
  • In the window “Delimited Record” we will provide the record delimiter, once again, we want to define the structure of Employee, i.e. each row is an Employee node, our limiter is this sample will be the {CR}{LF} (Carriage Return/Line Feed).
BizTalk Flat-File Schema Wizard Delimited Record positional
  • In the window “Child Elements” we will define what kind of element we want to assign to the registry. As we are defining the Employee structure and the file contains multiple employees, we have to select the “Element Type” as “Repeating record” If we do not perform this step, we will not have the ability to split the record into multiple elements / attributes individual.
    • Note: If you do not define the element as “Repeating record” (or “Record”), you will not have the ability to break down your record into individual elements or attributes.
BizTalk Flat-File Schema Wizard Child Elements positional

Note: earlier I told that we could select all the data inside the flat file and that we would need to treat the information differently in this step – option 2. So if you select this option, it will present all the lines in the document in this window, because we want to have an “Array” of Employees, in this step we need to select the “Element Type” of the first element as “Repeating record” and the rest of the set as “Ignore” (You set this to Ignore because you have already specified Repeating Record for the Employee node)

BizTalk Flat-File Schema Wizard Child Elements positional

In this point, we have defined the translation rules for the repeating record “Employee” but we have not broken down the various elements that compose the record itself. We have just map that each line of the text file corresponds to a record Employee.

  • In the “Schema View” select “Next” to continue processing the message
BizTalk Flat-File Schema Wizard Employee Record positional

At this point, everything is exactly the same as we did in the Delimited by Symbols example. From here forward is that the changes will emerge.

  • In this phase, the wizard restarts the whole process described above, but if you noticed, the wizard no longer selects all information contained in the text file, but only what was selected to define the record Employee. What we will do now is split the information of record “Employee” in different elements, for that, we will select only the information required leaving out the Carriage Return/Line Feed. On the “Select Document Data” screen click “Next”.
BizTalk Flat-File Schema Wizard Document Data positional
  • On the “Select Record Format” screen, because now the content of the “Employee” record needs to be parsed according to predefined length (positional), we need to select the option “By relative positions” and then click “Next”
BizTalk Flat-File Schema Wizard Record Format positional
  • The wizard provides a visual tool for showing and calculating the distance between the fields. On the “Positional Record” page, Use the left mouse button (or the right) to click a position marker in the position selection box to set the new position marker line. The position marker is represented by a solid line in will represent the rule in which a specific name field begins.
    • Position Marker selection box: Click an area on the page to represent the beginning position of the element. Click again to remove the position marker.
    • By default, a position marker line exists at the beginning of the record at position zero.
    • Click the following position markers to represent the rest of the data fields:
Field namePosition Marker
GroupId0
PrimaryKey4
SecondaryKey6
ControlFlag8
PartyNo9
Remark11
Country14
Name24
Surname36
Street52
City74
District86
ZipCode105
BizTalk Flat-File Schema Wizard Positional Record positional
  • In the “Child Elements” page, we will define the different elements/attributes of the structure of the record person. This operation is very similar to any XSD, where we can define the different names and data types. Adjust the values to:
    • Have the same name as the table above in the “Element Name”
    • Leave the default “Element Type” and “Date Type” – Field element and String
BizTalk Flat-File Schema Wizard Child Elements positional

Note: Once again, you should rename the parent node, each record, element or attribute in the BizTalk Flat File Schema Wizard, mainly in this step by modify the “Element Name” field to your desired name, however if you forgot to do that or if you make a mistake defining one of them, you can always rectify it in the Schema Editor. The same applies to the Data Type.

  • Select “Next” to continue.
  • Finally, the wizard will show the equivalent XML structure that your text file document will have. Once you select the option “Finish”, the scheme will be available for you to use in your BizTalk solution.
BizTalk Flat-File Schema Wizard Complete positional

After we finalize the creation of the Flat File Schema which will contain the translation/transformation rules of the text file, we can easily test our parsing rules, without having to get out of our development tool (Visual Studio) and without having to deploy our solution.

If we select the Flat File Schema that we just created and access to its properties, we can see that by default all properties are preconfigured so that we can perform tests to our message transformation:

  • The input instance file is configured with the file which was used to create the schema;
  • And with the correct input and output formats
    • Validate Instance Input Type: Native
    • Generate Instance Output Type: XML

To test, you simply right-click on the schema that we just created and select the option “Validate Instance”. This option will use the configured file to execute and validate all transformation rules defined in the Flat File Schema and present the final result or occurring errors in the “Output” window

BizTalk Flat-File Schema Wizard Testing positional

Did you know that Fields in positional records have a limit of 50000000 characters?

Creating the Pipeline that will be responsible for processing and transforming the text file

Once again we need to create at least one custom pipeline (receive pipeline) to process this flat-file document and translate it into is equivalent in XML.

To create the Receive Pipeline that will be responsible for processing and transforming the text file, we need to go to the BizTalk solution created in Visual Studio and perform the following steps:

  • Press the right button on top of the project in Solution Explorer, and select the option “Add –> New Item…”.
  • On “Installed Templates” menu in the window “Add New Item”, select the option “Pipeline Files”, and then select the option “Receive Pipeline”, then provide the name you want to give the pipeline in this example: “FF_Employee_ReceivePipeline.btp”
  • By selecting the option “Add”, the pipeline editor (BizTalk Pipeline Designer) will appear and that will let you view and add components to all the steps associated with receive pipeline: Decode, Disassemble, Validate and ResolveParty.

In this case, the pipeline that we will create it will be responsible for receiving a text file through a receive location and convert it to XML. For that, we will use the “Flat File Disassembler” component which is available in the Visual Studio Toolbox window (Ctrl+W, X).

  • Drag it into the Pipeline in step “Disassemble”.
BizTalk Flat-File Pipeline positional
  • Finally, select the component “Flat file disassembler”, go to its properties and on the “Document Schema” option: select the schema created earlier, in case the “PositionalTextFiles.FF_Positional_Employees”.
BizTalk Flat-File Pipeline positional

Note: If you want to create a send pipeline in order to transform an XML document into Flat File, we would follow the same steps, the difference is that we would have to drag the component “Flat File Assembler” on stage “Assemble”.

Once you have deployed your solution, you will want to ensure that you select the custom pipeline we just created in the Receive Locations that you expect to receive these flat files.

Now here it comes the same questions: Do you think the Schema is entirely correct? Does it handle all files accordingly to the expected results?

Or do we need to tune some of the properties of your schema… stay tuned to the next post.

Related posts:

You can download all the code used here from:

Flat Files Schemas: Dealing with Positional Flat FilesFlat Files Schemas: Dealing with Positional Flat Files
GitHub

Author: Sandro Pereira

Sandro Pereira lives in Portugal and works as a consultant at DevScope. In the past years, he has been working on implementing Integration scenarios both on-premises and cloud for various clients, each with different scenarios from a technical point of view, size, and criticality, using Microsoft Azure, Microsoft BizTalk Server and different technologies like AS2, EDI, RosettaNet, SAP, TIBCO etc. He is a regular blogger, international speaker, and technical reviewer of several BizTalk books all focused on Integration. He is also the author of the book “BizTalk Mapping Patterns & Best Practices”. He has been awarded MVP since 2011 for his contributions to the integration community.

2 thoughts on “BizTalk Server: Teach me something new about Flat Files (or not) – Positional Files”

  1. Hello Sandro
    Now, i need creat flatfile schema with Child Delimiter ‘/’ and can ignore some record or some element example
    Name/Year/Address/Day (header)
    AAA/2014/CCC/12 (record 1)
    AAA/CCC/12 (record 2, not include Year)
    Can you help me?

  2. Sandro,

    thanks for your blog. I haven’t gone through it completely yet, but was wondering if there’s a way to create the flat file schema from scratch. That is, without an instance? As you know, sometimes you’re assigned a task, but don’t have all the info (a sample file in my case) up front.

    Thnx in advance for any help or guidance,

    Hector

Leave a Reply

Your email address will not be published. Required fields are marked *

turbo360

Back to Top