Friday, February 18, 2011

Generating Flat Files With BizTalk 2009

Again, I was asked to produce a simple comma delimited file with BizTalk.  Easy, right? Well, it is once you know what you're doing, but for those of us (myself included) who rarely are asked to produce these flat files, it can be a bit challenging.  For one, we're used to XML formatting and generally writing to a database or simply producing an XML file.

So I can't stress enough how convenient it is leverage the Flat File Schema wizard.  Only catch is, you need a sample file to get the wizard rolling along. Just create a quick sample file with one row of data and the columns you need, and don't forget to add a carriage return after the last column.

What you get next is another window asking for a little clarification.  Little tricky here.  Ultimately, what you would like is a csv file that applies a carriage return after each row of data.  So select the entire row including the carriage return. Following that, make sure you are defining the record by a delimiter.  A screen will appear asking such, so populate the "child delimiter" field with a carriage return/line feed delimiter.

Moving right along. The next screen is very important. Here, you're defining the name of that root element (not that important) and the element type (important).  Select the Record element type, click next.

You'll go through the same screens for the next set of elements. Only difference here is that you won't be selecting the carriage return when asked to "Select Document Data," and your delimiter will be something else (usually a comma for comma delimited files). And of course your "Child Elements" will be defined as "Field element."  One other thing to note, ensure that your root element has the "child order" property set to "postfix."

From here your only other requirement is to define a send pipeline.  Why you ask? Because the send pipelines format your data according to the specifications defined within your schema.  But not to worry, all you have to know is that in your pipeline file, you simply need to drag a "Flat file assembler" on to the Assemble shape.  Once there, define the "Document schema" to that of what was defined by the Flat File Schema wizard.
Voila! You're ready to generate csv files....Unless of course you wanted to include column headers.  I can't stress enough how difficult it was to find resources that explained how these header schemas work.  Oh sure, define another schema with your column names and your done, right?  Not for all of us.  What I didn't understand was that even after I defined a header schema, 2 things were occurring; empty string values for column names and no carriage return.  
So I set off to find what was wrong with my definition.  What I found was that, I needed to define either the "Fixed" or "Default Value"  property for each element field.   But before I get into that, let me note that defining a header schema is as simple as taking a copy of your document schema and manipulating the values for Fixed or Default and ensuring that a PostFix value is defined for the child order of the root record element.  With that, your header values will print and your carriage return will push the first row of data below the column headers.  Now you're done. Well, almost.  Go back to your Send Pipeline and define the Header Schema property with the newly created Header Schema and now you can deploy and enjoy your BizTalk application.

3 comments:

  1. Thanks for taking the time to write this up. The wizard wasn't terribly intuitive. I expected it to be like the wizard from Access.

    ReplyDelete
  2. good stuf, saved my time, thanks

    ReplyDelete