The flat file is on a par with death and taxes in terms of its inevitability. BizTalk Server 2004 orchestrations are no exception; though the XML-driven elegance of Microsoft's highly-integrated message transport world might seem tainted somewhat by the inclusion of flat file messaging, there's no escaping it. Fortunately, BizTalk can accommodate flat file messages, both coming and going, without too much pain.
The reason systems integrators start BizTalking to begin with is to bring applications together into larger extended systems—and that, often by definition, means incorporating legacy system components. Many developers have learned the conveniences of using XML for intrasystem data transport, but the flat file will still pop up in the front, back, and middle of extended systems.
Pipelining flat files into an application
One of the key BizTalk rules of thumb is this: if a pipeline can do the job, then let it do the job. Pipelines basically get work done while data is being moved from one logical point to another. A message starts a journey as an EDI-formatted text file, and arrives at its destination as parsed XML. While this is by no means the only way to achieve the re-mapping of a message, it is certainly the most convenient.
Handling flat files in this way, whether inbound or outbound, requires two components: a schema defining the XML message that will either receive from or pass to the flat file, and a send or receive pipeline to implement it. (For simplicity's sake, we'll forego multipart messages for now and address them another time.)
Other BizTalk Server 2004 Resources:
- "Use BizTalk Explorer to the fullest to isolate business logic in distributed processes"
- "Utilizing XML documents in BizTalk 2004 orchestrations"
- "Migrating to BizTalk Server 2004"
- "Biztalk Server 2004 Architecture"
Flat file formats
BizTalk can handle any kind of flat file you're likely to throw at it: CSV, positional, even electronic data interchange (EDI). Again, for simplicity's sake, we'll stay with a very familiar example. View the file below as a handful of vendor master records:
1234567890, Acme Explosives, 123 Main Street, Anywhere, AZ
2468024680, Dangerous Toys, 456 Maple Ave, Nowhere, CA
1357913579, Coyote Medical Supplies, 789 Elm Street, NV
The first field is the vendor number; the rest are obvious. Note that we use delimiters to distinguish each field excepting the last one, where our end-of-line also denotes end-of-field (BizTalk doesn't require that it be this way, however, as we'll see below).
We want to turn this simple structure into a schema that generates an XML document, which the pipeline will then populate with data from an inbound flat file and which will then carry that data to an orchestration (where business logic can then go to work on it) or to some other application. Figure A shows what the schema would look like.
Here's how to generate it:
- Create a new project in BizTalk Explorer.
- Right-click on that project header in the Solution Explorer window; choose Add New Item | Schema and rename it (Vendors, for instance).
- Add elements per above; then select the Schema node and see the Properties window.
- There is a Schema Editor Extensions property under Advanced; select it, click on the ellipses; this gives you a window of extensions to choose, typically including EDI Schema Editor Extension, Flat File Extension, and Hws Extension; check the box to the left of Flat File Extension.
- Select the Vendors node; see the Properties window.
- Set Min Occurs to 1; set Max Occurs to Unbounded.
- Make certain the Structure property for the record is set to Delimited.
Now the delimiters must be set. In the Properties window, enter the following settings:
- Child delimiter - ,
- Child delimiter type - Character
- Child order - Infix (This specifies that in-line field delimiters, commas, between fields, but not after the last field in the line; Prefix specifies a leading delimiter/comma, and Postfix says that there will be a trailing comma delimiter after the last field in the line in addition to CR/LF.)
Finally, you need to define end-of-line for the root node; select the root element (Schema) and in the Properties window, enter the following settings:
- Child delimiter - 0x0D 0x0A (defines CR/LF)
- Child delimiter type - Hexadecimal
- Child order - Infix
After saving the schema, it's a good idea to validate it against the sample data above (or something similar) as a native instance (To configure and execute this validation, just right-click on the *.xsd file you've made of this schema in Solution Explorer, select Properties, and fill in the blanks—then right-click on the *.xsd file again to validate.)
Installing the pipeline
Now that there's a schema defined to map the data from the "vendors" flat file, a receive pipeline is needed execute it. To create this pipeline, right-click on your project in Solution Explorer and select Add New Item | Receive Pipeline. Name your pipeline and click Open to get a drag-and-drop configuration window as shown in Figure B.
You'll see a toolbox of pipeline components to the left. Drag and drop Flat File Disassemble from the toolbox into the Disassemble section of the configuration window. Now select Flat File Disassemble and see the Properties window. Under Pipeline Component Properties, enter the name of the schema created above under Document Schema.
Deploying your receive pipeline
Now we have a schema that creates XML to contain our flat file data, and a pipeline to use it. It remains to deploy that pipeline usefully.
There are two ways to do it, and it depends upon the application that is going to make use of the populated XML documents that carry the data that originated in the flat file. Either that data is going to be piped into an orchestration (where business logic resides in BizTalk applications) or it will forwarded to some application beyond the one where it is being imported (in which case, the receive pipeline will be used in BizTalk Explorer only, not in an orchestration).
To incorporate the pipeline in BizTalk Explorer, you need to define a receive location:
- Expand the BizTalk Explorer tree; right-click on Receive Ports.
- Add Receive Port (specify One-Way Port or Request-Response Port) and click OK (name the port in the resulting window).
- Open the new port under Receive Ports; right-click on Receive Locations.
- Add Receive Location | Receive Pipeline (specify your new Receive Pipeline here).
To incorporate the pipeline in a BizTalk orchestration, double-click the orchestration name under your project in Solution Explorer:
- If a Receive shape and Port are already deployed, click on the Port shape (otherwise drag-and-drop them into the orchestration).
- If the Port already exists, click on it and see the Properties window; enter the name of your pipeline under Receive Pipeline.
- If the Port is new, the Port Configuration Wizard will appear, and under Port Binding, entering Specify Now will give you a prompt to select your Receive Pipeline.
This should get you underway, working with flat files. We'll look at positional flat files and multi-part messaging from flat files in a future article.
Scott Robinson is a 20-year IT veteran with extensive experience in business intelligence and systems integration. An enterprise architect with a background in social psychology, he frequently consults and lectures on analytics, business intelligence and social informatics, primarily in the health care and HR industries.