Biztalk Messaging Services: Flat File Schema

Introduction

This article is intended to consolidate the principles of messaging in Biztalk Server 2006. Now we're going to analyze the business scenario where you are integrating data from a variety of sources, and some of these sources are legacy applications that may not have XML as the communication data format. One common format we deal with is the flat file.

A flat file is an ASCII text file consisting of records of single type and there is no structure information about the relationships among the records, unlike XML which has a well defined structure. It consists of one or more records and each record contains one or more fields. Each field may contain a value or it is omitted. The width of the fields may be fixed or variable. In a fixed width file there is neither field delimiter and nor record delimiter. Empty spaces are filled with pad characters. In a variable width file there is a special character as delimiters (fields or records) such as tab character, the comma or the pipe character. The most common example is the CSV file.

Let's create a sample flat file that is comma-separated for storing information about customers as shown in Listing 1. You can see that we have four records with three fields specifying Full name, Age and State of each customer.

Full name	Age	State
John Olamendy	28	Md
Peter Patterson	30	Ca
Peter Jones	38	Ca
John Smith	34	Ca

Listing 1

Now we're going to create a flat file schema through the Flat File Schema Wizard.

• Start Visual Studio 2005.
  
• Create a Biztalk Project and enter the name Flat_File_Schemas.
  
• In order to create the schema, click on the Project | Add New Item menu and select Schemas Files in the left panel and Flat File Schema Wizard in the right panel. Name the file CustomerFlatFile as shown in Figure 1.

Figure 1

• The Welcome page appears. Click Next to continue.
  
• In the Flat File Schema Information page, click on the Browse button to locate the sample file created before. Set the Record Name to Customers. Accept the default settings for Target Namespace and Code Page options as shown in Figure 2. Click Next to continue.

Figure 2

• In the Select Document Data page, you select the actual text for which you would like to create a schema (leave outside the metadata) as shown in Figure 3. Click Next to continue.

Figure 3

• In the Select Record Format, select By delimiter symbol option because each line ends with a carriage return line feed (CRLF) as shown in Figure 4. Click Next to continue.

Figure 4

• In the Delimited Record page, you specify the delimiter used in the text file. Select {CR}{LF} option in the Child Delimiter drop-down list as shown in Figure 5. Click Next to continue.

Figure 5

• In the Child Elements page, you tell the wizard which rows are repeating. Set the Element Type of the first row to Repeating Record and set the Element Name on the first row to a meaningful name such as Customer. Because the other rows are simply repeating the same format as the first, you need to set the Element Type to Ignore as shown in Figure 6. Biztalk will skip the data when generating the schema.

Figure 6

• Biztalk recognizes that you have a record, thus it needs more information for further configuration. In the Schema View page, click Next to continue.
  
• The Select Document Data page is shown again, but this time for defining the Customers/Customer record. Select the first row, and click on Next to continue as shown in Figure 7.

Figure 7

• In the Select Record Format, choose By delimiter symbol option (in this case it is the comma) and click Next to continue as shown in Figure 8

Figure 8

• In the Delimited Record page, select the comma as the Child delimiter as shown in Figure 9.

Figure 9

• In the Child Elements page, declare the element name, element type and data type for each item as shown in Figure 10.

Figure 10

• And finally, the wizard doesn't need any information, the click on Finish button as shown in Figure 11.

Figure 11

Conclusion

This article explained the principles of Flat File and the creation of the underlying XML schema using the Flat File Schema Wizard. This wizard is one of the major improvements in Biztalk Server 2006.