Click here to Skip to main content
12,697,294 members (28,758 online)
Click here to Skip to main content
Add your own
alternative version

Stats

93.1K views
560 downloads
32 bookmarked
Posted

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

, 20 Apr 2006 CPOL
Rate this:
Please Sign up or sign in to vote.
An article explaining how to generate a flat file schema in BizTalk Server 2006 using the Flat file schema wizard

Introduction

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.

Background

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

AddNewItem.PNG

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.

Wiz_1.PNG

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

Wiz_2.PNG

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

Wiz_3.PNG

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

Wiz_4.PNG

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

Wiz_5.PNG

Step 8: The top level elements are being depicted.

Wiz_6.PNG

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

Wiz_7.PNG

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

Wiz_8.PNG

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.

Wiz_9.PNG

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

Wiz_10.PNG

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

Wiz_10.PNG

Schema Validation

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

Takeaways

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.

License

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

Share

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)
http://biztalkcontrolcenter.codeplex.com

You may also be interested in...

Pro
Pro

Comments and Discussions

 
Suggestionhow to create schema for excel input file? Pin
sangeetha m1-Nov-12 1:51
membersangeetha m1-Nov-12 1:51 
QuestionFlat File Problem. Pin
Rahul Madaan24-Jun-11 3:37
memberRahul Madaan24-Jun-11 3:37 
Onboard Container List for Vessel Agent Date : 23/02/2010 09:25:05

Vessel: CSAV LARAQUE Voyage: 01004S
Slot Owner: CA Port of Disc: ARBUE//
ATU : 02/02/2010 21:15

SEQ CONTAINER NO CELL LC WT ST SZTY SO OP PDEST SEAL NO REMARKS
---- ------------ ------- -- -- ---- -- -- ----- ------- -------
1 CRLU 1271672 0380382 22.3 F/T 4530 CA CA ARBUE +018C
2 TTNU 9656723 0380384 13.0 F/T 45G1 CA CA ARBUE 9'6'

Total Containers for ARBUE//:2

Port of Discharge: BRITJ//

1 FCIU 2169073 0370904 21.2 F 2200 CA CA BRITJ NA
2 IPXU 3462194 0170990 7.3 F/T 2200 CA CA BRITJ
3 IPXU 3840993 0390906 9.7 F/T 2200 CA CA BRITJ
4 TTNU 1315309 0390904 27.2 F/T 22G1 CA CA BRITJ
5 TTNU 1980736 0370906 19.4 F/T 22G1 CA CA BRITJ

6 CAXU 7466062 0380908 21.0 F/T 42G1 CA CA BRITJ
7 CLHU 4632954 0380910 21.0 F/T 4310 CA CA BRITJ
8 CLHU 4648462 0380912 19.4 F/T 4310 CA CA BRITJ
9 TGHU 8550090 0380914 10.7 F/T 4500 CA CA BRITJ 9'6'
10 CLHU 8575282 0380712 13.2 F/T 4510 CA CA BRITJ 9'6'
11 TCKU 9244462 0380710 25.1 F/T 45G1 CA CA BRITJ 9'6'

Total Containers for BRITJ//:11

Port of Discharge: BRPNG//

1 GSTU 3480110 0390312 19.4 F/T 2200 CA CA BRPNG
2 IPXU 3421467 0370312 19.4 F/T 2200 CA CA BRPNG
3 IPXU 3808899 0370302 19.4 F/T 2200 CA CA BRPNG
4 BSIU 2116384 0370502 19.4 F/T 22G1 CA CA BRPNG
5 CAXU 6666127 0370310 19.4 F/T 22G1 CA CA BRPNG
6 CRXU 3161579 0390306 19.4 F/T 22G1 CA CA BRPNG
7 FSCU 7566998 0390308 19.4 F/T 22G1 CA CA BRPNG
8 FSCU 7567696 0370306 19.4 F/T 22G1 CA CA BRPNG
9 FSCU 7655457 0370304 19.4 F/T 22G1 CA CA BRPNG
10 GLDU 3146068 0390502 19.4 F/T 22G1 CA CA BRPNG
11 GLDU 5258811 0370308 19.4 F/T 22G1 CA CA BRPNG
12 TTNU 1534950 0390310 19.4 F/T 22G1 CA CA BRPNG
13 TTNU 1586851 0390302 19.4 F/T 22G1 CA CA BRPNG
14 TTNU 3097633 0390304 19.4 F/T 22G1 CA CA BRPNG

15 INKU 2547909 0380314 15.2 F/T 4500 CA CA BRPNG 9'6'
16 INKU 2553033 0380504 17.4 F/T 4500 CA CA BRPNG 9'6'
17 FSCU 9539395 0380510 16.2 F/T 4510 CA CA BRPNG 9'6'
18 INKU 6415270 0380506 17.1 F/T 4510 CA CA BRPNG 9'6'
19 INKU 6419378 0380708 16.2 F/T 4510 CA CA BRPNG 9'6'
20 FCIU 8043816 0380704 17.1 F/T 45G1 CA CA BRPNG 9'6'
21 INKU 6135875 0380512 15.7 F/T 45G1 CA CA BRPNG 9'6'
22 TCKU 9343850 0380508 16.4 F/T 45G1 CA CA BRPNG 9'6'
23 TCNU 9411672 0380706 16.7 F/T 45G1 CA CA BRPNG 9'6'
24 TGHU 8807531 0380702 16.3 F/T 45G1 CA CA BRPNG 9'6'

Total Containers for BRPNG//:24

Port of Discharge: BRRIG//

1 CRXU 3161141 0391006 6.9 F 2200 CA CA BRRIG M478862
2 FCIU 2083025 0371006 23.9 F/T 2210 CA CA BRRIG
3 DFSU 2080478 0391004 23.9 F/T 22G1 CA CA BRRIG
4 TTNU 1916450 0371004 23.9 F/T 22G1 CA CA BRRIG

5 BMOU 9205024 0381008 28.3 F 4530 CA CA BRRIG M478481

Total Containers for BRRIG//:5

Port of Discharge: BRSSZ//

1 BSIU 2257309 0071284 14.3 F/T 2200 CA CA BRSSZ
2 FSCU 7822955 0390806 4.3 F/T 2200 CA CA BRSSZ
3 GESU 3482209 0390606 4.4 F 2200 CA CA BRSSZ M478868
4 GVCU 2278908 0390804 7.0 F/T 2200 CA CA BRSSZ
5 IPXU 3800460 0370604 22.9 F/T 2200 CA CA BRSSZ
6 IPXU 3807995 0390802 22.9 F/T 2200 CA CA BRSSZ
7 BSIU 2088003 0390404 19.6 F/T 2210 CA CA BRSSZ
8 FSCU 3738846 0071286 10.2 F/T 2210 CA CA BRSSZ
9 SCZU 7719554 0390602 22.9 F/T 2210 CA CA BRSSZ
10 EXFU 0512007 0370402 25.0 F/T 2270 CA CA BRSSZ
11 TIFU 1489585 0370404 23.0 F/T 2270 CA CA BRSSZ
12 EXFU 0707061 0390402 24.0 F/T 2271 CA CA BRSSZ
13 FCIU 2102629 0370606 11.5 F/T 22G1 CA CA BRSSZ
14 FSCU 7571280 0370602 22.9 F/T 22G1 CA CA BRSSZ
15 FSCU 7776926 0071282 22.9 F/T 22G1 CA CA BRSSZ
16 FSCU 7783817 0370406 15.1 F/T 22G1 CA CA BRSSZ
17 TTNU 1342752 0390406 13.8 F/T 22G1 CA CA BRSSZ
18 TTNU 2494507 0390604 14.0 F/T 22G1 CA CA BRSSZ

19 GVCU 4036502 0381012 11.0 F/T 4200 CA CA BRSSZ
20 TTNU 5070056 0381014 8.9 F 4200 CA CA BRSSZ M478479
21 TTNU 5760792 0381010 12.0 F/T 42G1 CA CA BRSSZ
22 GATU 8645812 0380682 9.6 F/T 4500 CA CA BRSSZ 9'6'
23 BSIU 9055038 0380610 26.0 F/T 4510 CA CA BRSSZ 9'6'
24 CLHU 8891423 0380482 10.1 F/T 4510 CA CA BRSSZ 9'6'
25 TRLU 5289336 0380410 27.0 F/T 4510 CA CA BRSSZ 9'6'
26 GLDU 7599611 0380612 23.0 F/T 45G1 CA CA BRSSZ 9'6'

Total Containers for BRSSZ//:26

Port of Discharge: ZADUR//

1 GLDU 0769526 0380584 9.0 F/T 45G1 CA CA ZAJNB 9'6'

Total Containers for ZADUR//:1

SHUT-OUT CONTAINERS

Vessel: CSAV LARAQUE Voyage: 01004S
Slot Owner: CA Port of Disc: ZADUR//
ATU : 02/02/2010 21:15

SEQ CONTAINER NO CELL LC WT ST SZTY SO OP PDEST SEAL NO REMARKS
---- ------------ ------- -- -- ---- -- -- ----- ------- -------

Total Containers for ZADUR//:1

Port of Discharge: BRSSZ//

1 GESU 3584828 0390906 24.1 F/T 2200 CA CA BRSSZ
2 IPXU 3128464 0390704 24.1 F/T 2200 CA CA BRSSZ
3 IPXU 3309307 0390904 24.1 F/T 2200 CA CA BRSSZ
4 IPXU 3862170 0390304 24.1 F/T 2200 CA CA BRSSZ
5 FSCU 7675387 0390506 24.1 F/T 2210 CA CA BRSSZ
6 GVCU 2131856 0390706 24.1 F/T 2210 CA CA BRSSZ
7 BSIU 2172942 0390302 24.1 F/T 22G1 CA CA BRSSZ
8 FCIU 3593240 0390702 24.1 F/T 22G1 CA CA BRSSZ
9 FSCU 7579064 0390502 24.1 F/T 22G1 CA CA BRSSZ
10 INBU 3852592 0390504 24.1 F/T 22G1 CA CA BRSSZ

Total Containers for BRSSZ//:10

Total no. of shut-out containers: 10

Total number of records (excluding shut-out): 69

** END OF LIST **


I have a file of this structure and I am facing problem in Processing this file through BizTalk.
I have to receive this file and make a Schema like this file.Can anyone help me Plz......ASAP.
GeneralFlat File Schema Creation Problem Pin
Rahul Madaan5-Apr-11 0:14
memberRahul Madaan5-Apr-11 0:14 
QuestionFlat file error remaining stream has unrecongnizable data Pin
KenH1114-Jun-10 9:24
memberKenH1114-Jun-10 9:24 
Questionhow To Produce Flat File From Xml Pin
Aunalisiraj9-Mar-08 19:50
memberAunalisiraj9-Mar-08 19:50 
Questionflat file parser.... Pin
Deven Bhatt22-Oct-07 0:02
memberDeven Bhatt22-Oct-07 0:02 
GeneralExcellent article Pin
Santosh Benjamin16-May-07 10:30
memberSantosh Benjamin16-May-07 10:30 
GeneralZzzzzzzzzzz Pin
Heywood21-Apr-06 11:49
memberHeywood21-Apr-06 11: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.

| Advertise | Privacy | Terms of Use | Mobile
Web02 | 2.8.170118.1 | Last Updated 20 Apr 2006
Article Copyright 2006 by Naveen Karamchetti
Everything else Copyright © CodeProject, 1999-2017
Layout: fixed | fluid