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

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 creating or opening 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 the Flat File Schema Wizard to generate a schema based on a positional flat file

To create the schema that 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 the 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 explained previously, by selecting this option, we will be guided automatically by the BizTalk Flat File Schema Wizard, which 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.

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

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.

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

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 window and set a different code page, for example, Western-European (1252) instead of UTF-8 (65001).

• 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 carriage return/Line Feed ({CR}{LF}). So, in this case, we will have to select the By delimiter symbol option.

• 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 for this sample will be the {CR}{LF} (Carriage Return/Line Feed).

• 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 individually.
  • Note: If you do not define the element as a Repeating record (or Record), you will not have the ability to break down your record into individual elements or attributes.

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

At 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 mapped that each line of the text file corresponds to a record of an employee.

• In the Schema View, select Next to continue processing the message.

At this point, everything is exactly the same as we did in the Delimited by Symbols example. From here forward, 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 the record Employee into 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.

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

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

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

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

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

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 its 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 the 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 the receive pipeline: Decode, Disassemble, Validate, and ResolveParty.

In this case, the pipeline that we will create will be responsible for receiving a text file through a receive location and converting 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.

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

Note: If you want to create a send pipeline in order to transform an XML document into a flat file, we would follow the same steps; the difference is that we would have to drag the component Flat File Assembler onto the 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 where you expect to receive these flat files.

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

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

Download

You can download all the code used here from GitHub:

Hope you find this helpful! If you liked the content or found it useful and would like to support me in writing more, consider buying (or helping to buy) a Star Wars Lego set for my son. 

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. View all posts by Sandro Pereira