Click here to Skip to main content
15,397,791 members
Articles / Operating Systems / Windows
Posted 6 Apr 2006


32 bookmarked

Creating Flat File schemas using the BizTalk Server 2006 Flat File Schema Wizard

Rate me:
Please Sign up or sign in to vote.
3.55/5 (17 votes)
20 Apr 2006CPOL4 min read
An article explaining how to generate a flat file schema in BizTalk Server 2006 using the Flat file schema wizard


An accurate Flat File schema is very important in every integration project. Several legacy projects have been operational on Flat file format, converting these flat files into XML is vital to the success of any integration project. The new Flat file schema Wizard in BizTalk 2006 is just the right tool for the job. This article shall attempt to explain the various features and things to look out for while creating a flat file schema using the Wizard.


In case you are a newbie to this flat file structures, please read the following articles on Flat file schemas...

Flat File Structure - Quick Intro...

A flat file, unlike an XML file, does not have any visible inherent structure. A flat file's structure is evident from its usage and also requires some domain knowledge to understand its representation. A flat file structure is of several types:

  1. Delimited flat file
  2. Positional flat file
  3. A flat file with combination of Delimited and Positional records

In this article, we shall generate a schema for a medium-complex flat file using the BizTalk 2006 Flat File Wizard.

Example - 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   
  1. In the example above, there are 5 lines, each line represents a "record" of information.
  2. A Positional flat file is one whose fields are placed in positions (columns), whose field lengths are fixed in size. The "ID" field is of fixed size of 10 characters, the "Description" field is of fixed size of 10 characters, the "DOB" is of fixed size of 10 characters and the "Full Name" is of fixed size of 20 characters.
  3. Notice that every record is of different type/structure.

The Data Dictionary for the Flat File

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

Using the BizTalk 2006 - Flat File Schema Wizard

Create a new BizTalk Server Project in Visual Studio 2005.

Step 1: In the Visual Studio 2005 menu, select the File -> New -> Project -> and select "Empty BizTalk Server Project" and type the name "FFSchemaDemo".

Step 2: In the Solution Explorer, right click on the project name "FFSchemaDemo" and select Add -> New Item. In the "Add New Item" dialog box, select the "Flat File Schema Wizard" and type the Name as "FFSchema_Wiz.xsd".


Step 3: In the screen that appears, specify the flat file (*.txt) location. This will help the Wizard load the file for parsing. Specify the ROOT (Record name) element name for the schema. Observe the Red boxes in the screen shot.


Step 4: Notice the entire file being loaded into the Wizard with the New line separators (CRLF) being marked visually.


Step 5: Every line in the flat file is delimited by CRLF, whereas every field within a line is positional. Hence select "By delimiter symbol".


Step 6: Since every line is separated by CRLF characters, specify this option in the Child Delimiter drop down text box.


Step 7: Defining Child elements is the most critical part of the Wizard steps. In the example that we considered, there is one Header line, two detail lines, one total line and one end line. The Element name specifies the top level elements that appear in the final schema. The Element type, in the case of a HDR is "Record", since it contains several other positional fields. The Element type for "DTL" is a "Repeating record" since the similar kind of record is repeated on the third line in the file. The Element type for the third line is set to "Ignore", since "DTL" is being repeated on the third line. Note that the "Ignore" option is designed, for this purpose alone. For the subsequent lines, the Element type is set to "Record".


Step 8: The top level elements are being depicted.


Step 9: The first line is selected, notice that the line separators are not being selected by default.


Step 10: We need to specify that the fields in the selected record are positional.


Step 11: The sizes for each field in a positional record and the Tag Identifier, used to identify each line are being specified. The arrows which fix the column sizes can be toggled by a mouse click. Important : Note that, if there are five fields, then you would need to fix only four arrows.


Step 12: The Field names and types need to be specified for each field.


Last Step: Similar steps need to be followed to complete the field definitions for all the lines in the file.


Schema Validation

Once the schema has been generated, validate the schema by right clicking on the file and selecting "Validate Schema" menu option.


The Flat file schema wizard simplifies schema creation. NOTE that the schema wizard expects the complete instance of the flat file to be present for the schema generation. If there are any optional records, they would need to be mapped manually once the schema is created.

About the Downloadable Code

  • Unzip the zip file in the C:\ drive.
  • The flat file sample is placed in the FlatFilesInput directory in the zip file.


This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


About the Author

Naveen Karamchetti
Architect AT&T Wi-Fi Services
United States United States
Naveen has done his Masters (M.S.) in Computer science, has started his career programming the mainframes and now has more than a decade of programming, development and design experience. Naveen has a sharp eye and keen observation skills. Naveen has worked for several companies and strived hard to build large scale business applications and bringing better solutions to the table.
Quite recently Naveen has built a fairly complex integration platform for a large bank. His hobbies include training, mentoring and research. Naveen spends his free time visiting National Parks nationwide.

Naveen has developed the BizTalk Control Center (BCC)

Comments and Discussions

Suggestionhow to create schema for excel input file? Pin
sangeetha m1-Nov-12 0:51
Membersangeetha m1-Nov-12 0:51 
QuestionFlat File Problem. Pin
Rahul Madaan24-Jun-11 2:37
MemberRahul Madaan24-Jun-11 2:37 
GeneralFlat File Schema Creation Problem Pin
Rahul Madaan4-Apr-11 23:14
MemberRahul Madaan4-Apr-11 23:14 
QuestionFlat file error remaining stream has unrecongnizable data Pin
KenH1114-Jun-10 8:24
MemberKenH1114-Jun-10 8:24 
Questionhow To Produce Flat File From Xml Pin
Aunalisiraj9-Mar-08 18:50
MemberAunalisiraj9-Mar-08 18:50 
Questionflat file parser.... Pin
Deven Bhatt21-Oct-07 23:02
MemberDeven Bhatt21-Oct-07 23:02 
GeneralExcellent article Pin
Santosh Benjamin16-May-07 9:30
MemberSantosh Benjamin16-May-07 9:30 
GeneralZzzzzzzzzzz Pin
Heywood21-Apr-06 10:49
MemberHeywood21-Apr-06 10:49 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.