Flattening out the complexity in flat file schemas in BizTalk 2004 - Part 2

• Download source files - 5.64 Kb

Introduction - First things first!

In Part 1 of this article series, we have seen how to write BizTalk schemas for delimited flat files. In Part 2 of the series, we shall see how to write schemas for positional flat files in BizTalk Server 2004.

Positional flat file structure

In a positional flat file, the record fields are of fixed size. It is similar to a series of columns wherein the data needs to be fitted in. Columns of data are defined based on the size of the data that is to be fitted into these columns. Let us work out an example of positional files.

List of examples:

1. Example 1 - A simple positional flat file.
2. Example 2 - A complex positional flat file.

Example 1 - A simple positional flat file

12345678901234567890123456789012345678901234567890
JOHN DOE            1964-10-05CLAYTON             
ROBERT B            1978-11-10EDWARD STREET       
JOHN LENON          1927-02-30WORTHING            
EDMOND DANTES       1910-09-12COVENTRY            
SIR CHAMBERS        1934-05-18HARRODS

1. A positional flat file is one whose fields are placed in positions (columns), and whose field lengths are of fixed size. In the above example, the "name" field has a fixed size of 20 characters, the "date-of-birth" field has a fixed size of 10 characters and the "place of origin" has a fixed size of 20 characters.

   Note: The column numbers at the top of the file are shown only for indication purposes.

2. A positional flat file record must always be a child of the delimited record. The delimiter character specified for the parent-delimited record must not appear in the data of the child positional record. There is no way to escape the delimiter character of the parent-delimited record in the data of a child positional field.

Creating the BizTalk flat file schema solution

Create a new BizTalk Server Solution in Visual Studio.

Step 1: In the Visual Studio .NET menu, select the File -> New -> "Blank Solution" and type the name "FFSchemas":

Step 2: In the Solution Explorer, right click on the solution name "FFSchemas" and select Add -> New Project. In the "Add Project" dialog box, for the type of the project, select "BizTalk Projects". Select the template "Empty BizTalk Project" and create a project named "FlatFileSchema".

Building the schemas - Example 1

We shall create the schema based on the example 1.

Step 1: Right-click on the project in the Solution Explorer and select the "Add New Item" option. Then, select the item "Schema" and name it "FFSchema_POS". When the schema shows up, rename the "Root" element to "POS".

Step 2: Select the item "Schema" and right-click, select Properties. Change the property "Schema Editor Extensions" to Flat File Extension:

Step 3: Select the item "POS" and right-click, and select Properties:

Note: Even though this is a positional flat file, the structure property = "Positional" is only for the fields of a record and not for the record itself, since the records are delimited by the [CRLF] characters.

Step4: Select the item "POS" and right-click -> Insert Schema Node -> Child Record. Name the record as "Record" and create the child elements: "Name", "DOB" and "Address":

Step 5: Select the item "Record" and right-click, select Properties. Set the properties as shown in the table below. The property structure is set to "Positional". We need to support multiple records and hence we set the Max Occurs to "*" or "unbounded":

Step 6: Select the item "Name" and right-click, select Properties. Set the properties as shown in the table below. You would need to repeat the same for the elements "date-of-birth" and "place of origin".

Note: The same set of properties need to be set for the elements "date-of-birth" and "place of origin".

Step 7: In the Solution Explorer, select the schema "FFSchema_POS.xsd" and right-click -> select "Properties". In the property pages screen, select the properties as shown in the image. For the "Input Instance File Name" -> choose the path where the input files (*.txt) are present:

Validating and testing the schema created

Once we have finished writing the schema, we need to validate and test the schema.

Step 1: In the Solution Explorer, select the schema "FFSchema_POS.xsd" and right-click -> select "Validate Schema". Observe the output window and you would notice a message starting with "Validate Schema succeeded for file...".

Step 2: In the Solution Explorer, select the schema "FFSchema_POS.xsd" and right-click -> select "Validate Instance". Observe the output window and you would notice a message starting with "Validate Instance succeeded for schema FFSchema_POS.xsd...". Now click on the link which starts with the message "Validation generated XML output..."

The XML output file would look like this:

Example 2 - A complex positional flat file

810HDR    Invoice   1972-05-12John Doe            
810DTL    01-2304040200       $2.34     
810DTL    02-4030400400       $1.34     
810TOT    600       $1004     
810END    Steven    
810HDR    Invoice   1936-06-15Edmond Dantes       
810DTL    01-2993030150       $1.23     
810DTL    02-202047676        $0.54     
810TOT    226       $225.54   
810END    Steven

1. The example shown above is a complex kind of a positional flat file which is a somewhat stripped down version of a real life application. The structure of this file can be divided into several parts:
   • Header Part starting with "810HDR".
   • Detail Part starting with "810DTL".
   • Total Part starting with "810TOT".
   • End Part starting with "810END".
2. The detailed structural break up of the complex flat file example is given below:

   810HDR (Total = 50 characters)
       - Header ID       - 10 characters
       - Description     - 10 Characters
       - Date of Birth   - 10 Characters
       - Full Name       - 20 Characters
   
   810DTL (Total = 40 characters)
       - Detail ID       - 10 characters
       - Part No         - 10 characters
       - Quantity        - 10 characters
       - Price per unit  - 10 characters
   
   810TOT (Total = 30 characters)
       - Total ID        - 10 characters
       - TotalQuantity   - 10 characters
       - TotalPrice      - 10 characters
   
   810END (Total = 20 characters)
       - End ID          - 10 characters
       - ClerkName       - 10 characters

3. The Header starting with "810HDR" is of length '50' characters and is being broken up into "Header ID", "Description", "Date of Birth" and "Full Name".

Building the schemas - Example 2

We shall create the schema based on the example 2.

Step 1: Right-click on the project in the Solution Explorer and select the "Add New Item" option. Then, select the item "Schema" and name it "FFSchema_Group". When the schema shows up, rename the "Root" element to "GRP".

Step 2: Select the item "Schema" and right-click, select Properties. Change the property "Schema Editor Extensions" to Flat File Extension:

Step 3: Select the item "GRP" and right-click, select Properties:

Step 4: Select the item "GRP" and right-click -> Insert Schema Node -> Child Record. Create several child records and name the records as "HDR", "DTL", "TOT" and "END":

Step 5: Select the item "HDR" and right-click, select Properties. Set the properties as shown in the table below. The "Tag Identifier" is used to identify the record starting with the tag "810HDR". Since every record has a unique identifier, this property can be set to identify the correct record:

Note: The positional length value needs to be set for the individual elements under the "HDR" node and similarly for all other nodes.

Step 6: In the Solution Explorer, select the schema "FFSchema_Group.xsd" and right-click -> select "Properties". In the property pages screen, select the properties as shown in the image. For the "Input Instance File Name" -> Choose the path where the input files(*.txt) are present:

Validating and testing the schema created

Once we have finished writing the schema, we need to validate and test the schema.

Step 1: In the Solution Explorer, select the schema "FFSchema_Group.xsd" and right-click -> select "Validate Schema". Observe the output window and you would notice a message starting with "Validate Schema succeeded for file...".

Step 2: In the Solution Explorer, select the schema "FFSchema_Group.xsd" and right-click -> select "Validate Instance". Observe the output window and you would notice a message starting with "Validate Instance succeeded for schema FFSchema_Group.xsd...". Now click on the link which starts with the message "Validation generated XML output..."

The XML output file would look like this:

Quick takeaways

1. Set the schema's editor extensions property before you start with the flat file schema.
2. Set "Child Delimiter Type" property to Hexadecimal to avoid character ambiguity.