I have found the Flat File Schema Wizard in BizTalk Server 2006 and BizTalk Server 2006 R2 to be extremely useful and figured that I would blog about it for anyone looking for an introduction to this feature.
Inside of BizTalk, the preferred message type is XML. If messages are in an XML format, BizTalk can do intelligent things with the messages. These "intelligent things" include routing the message based upon a promoted property, tracking elements in BAM or making logic decisions inside of an orchestration based on a particular value of a node.
Generally the way Flat Files are processed at Run-time are:
- Flat file is received by adapter configured to watch the receive location's URI
- A custom pipeline is required to disassemble Flat File into its XML equivalent
- BizTalk Orchestration(if non-pure messaging scenario chosen) would then pick up this XML equivalent version of the flat file. The message would then move through the business process.
- If/when BizTalk needed to send out a version of this XML message as a Flat file, a custom Send Pipeline would be required that would take the XML message and assemble the Flat File.
- In the Send Port adapter, this custom Send Pipeline would need to be configured in order for the Flat File to be delivered to the end point.
I will now walk you through what is required at design time in order to enable the runtime to support the processing of Flat Files:
1. In your Schema and Maps project, "Add a New Item"
2. On the left hand side, select "Schema Files" and then select "Flat File Schema Wizard" from the right hand side. In the bottom centre of the wizard, provide a name for this Flat File Schema.
3. The Wizard based interface will now appear which will walk you through defining your Flat File structure. Click on the "Next" button to continue.
4. You will need an instance, or sample, of the flat file that you are about to create a schema for. Browse for this file, provide a "Record" name and update the "Target namespace" as required.
5. The wizard will then load up your instance file to further break it down. In this step, you need to define how records are differentiated. Depending upon the complexity of your flat file, your record definition may be different from mine.
For my sample, the structure of my record is as follows:
CustomerID,DiscountGroup,Address,City,EmailAddress,PhoneNumber{CR}{LF}. Since each record is contained within one line I want to select the entire first line.
6. I then need to indicate how the record is delimited. In my scenario it is based upon a Carriage Return/Line Feed. Something to note here is that my source file is based upon a Windows File structure. Unix indicates the end of a line differently than Windows.
7. In the next step, I am providing exactly how the child delimiter is defined. Since this is a Windows based file, I will select "{CR}{LF}" . Other options include {CR}, {LF}, {TAB}, {SPACE}, {0x1A}, {}, {.}, {;} or you can provide your own symbol.
8. So at this point, I have defined what a Record looks like but I have not broken down the various elements that make up a record. By default "Repeating record" is not selected, so you will want to pull down the "Element Type" drop down to select it. If you do not do this step, you will not have the ability to break down your records into individual elements/attributes.Also, if you want, rename the parent node for each record, modify the "Element Name" field to your desired name.
9. Select "Next" to continue to the next screen where you will be able to further define how each record is broken up.
10. So here is the start of breaking down the record into individual elements. You want to ensure that the {CR}{LF} symbols are not selected and click "Next".
11. Here is where you tell BizTalk how to parse the record. The two options are by delimiter symbol or relative. Since I am using a CSV file, I selected "By delimiter symbol". If you have a fixed width file, then you would want to select "By relative positions".
12. By default, the comma symbol is populated in the Child delimiter drop down. If you use a different like a pipe '' or a period '.' you can change this here.
13. In this screen, you are able to define each of the Elements/Attributes for your data. Much like any XSD, you are able to define the data types as you see fit. Also note, in the far right hand column, BizTalk is loading the contents of you sample row into the wizard for you to verify whether BizTalk has parsed it correctly.
14 BizTalk will then show you a break down of what your XML tree will look like. Once you click Finish the Schema will be available to you in your BizTalk solution.
15 You now have a Flat File Schema for use within BizTalk!
Since the Disassembling and Assembling of Flat Files occur within pipelines, having this Flat File schema will not do us much good until we create the pipelines to provide the conversion between Flat Files and XML.
I will now walk you through the process of creating a pipeline that will use the Flat File Schema that we just created.
In the sample project that I am using, I have 1 "master" solution and 4 projects that belong to the solution. I have broken it down based upon a popular project structure:
- MapsAndSchemas
- Orchestrations
- Pipelines
- Helper (.Net Assembly used for any helper/utility methods)
So within my Pipelines project, I need to add a reference to my Schemas project. This will allow me to use this schema in my Disassembly/Assembly stages of my pipeline.
1. Add a "New Item" to your Pipeline project(or applicable project)
2. On the left portion of the dialog box, select "Pipeline Files", In the top centre of the dialog box, select "Receive Pipeline" and then provide a meaningful name in the bottom of the dialog box.
3. The Pipeline component designer will load. You will then want to drag a "Flat file disassembler" control from your toolbox and release it in the "Disassemble" stage.
4. With the "Flat file disassembler" control selected, select your Flat File schema that you previously created from the "Document schema" drop down.
5. You would follow the same steps when creating your Send pipeline. The difference being that you would be dropping a "Flat file assembler" control onto the "Assemble" stage in the pipeline. You would select the same Flat File schema from the "Document schema" drop down.
Once you have deployed your solution, you will want to ensure that you select your custom pipelines in the Receive Locations/Send Ports that you expect to receive/send these flat files.
