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:
- Example 1 - A simple positional flat file.
- 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
- 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.
- 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:
POS "Root Node" properties
Property Name |
Property Value |
Child Delimiter Type |
Hexadecimal |
Child Delimiter |
0x0D 0x0A |
Child Order |
Default Child Order |
Structure |
Delimited |
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":
"Record" Node properties
Property Name |
Property Value |
Structure |
Positional |
Min Occurs |
1 |
Max Occurs |
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".
"Name" Node properties
Property Name |
Property Value |
Pad Character Type |
Hexadecimal |
Pad Character |
0x20 |
Positional Length |
20 |
Positional Offset |
0 |
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
- 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".
- 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
- 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:
GRP "Root Node" properties
Property Name |
Property Value |
Child Delimiter Type |
Hexadecimal |
Child Delimiter |
0x0D 0x0A |
Structure |
Delimited |
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:
"HDR" Node properties
Property Name |
Property Value |
Tag Identifier |
810HDR |
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
- Set the schema's editor extensions property before you start with the flat file schema.
- Set "Child Delimiter Type" property to Hexadecimal to avoid character ambiguity.