Thursday, May 25, 2006

Creating FF Schemas in Biztalk - A Different Approach

One of my projects requires integrating a file from a Bank into our financial system. The Bank file is a positional flat file with 8 records. The structure is as follow:


1 – File header
......2 – Transaction header
................5 – Voucher Line
................5A – Distribution Line
................5B - Vchr Tax Line
.......7 – Transaction trailer
8 – File Trailer


All the records can occur multiple times in the file. In past, I always had problems creating FF schemas for positional files especially in area of looping. Surprisingly the approach I took this time made it very easy . I will try to break it down in small easy steps:

I will not go in details of "postional" record entry but focus only on "Looping"

1) I used the “Bottoms-Up” approach (this is what I call it). I broke the file into smaller segments and designed starting from the deepest child element and then added other elements as I moved towards the top. i.e Record 5 to Record 1. And then I designed for rest of the file. i.e Record 5 to Record 8.

2) I first designed the schema for only Record 5. I assumed that the file has only 3 records that can repeat multiple times within a root level. The structure looks something like:



5, 5A and 5B are the positional records that repeat multiple times. So they will have to be under a “delimited” record that has max occurs as “unbounded”. This delimited record will have name “Level5” and Child Delimiter type as "Default Child Delimiter" and Child Order as "Infix". I used the same properties for all the "Delimited" levels.

3) Having designed Level 5, I then designed Record 2 . I followed the same principle. Since Record 2 is parent of Record 5, I placed it one level higher than Record 5 but at the same level as the delimited record “Level5”. Record 2 is positional record like Record 5 and can occur multiple times. So it will again go under “delimited” record that has properties as “unbounded”. We will name it as “Level7_2”. As it shared by Record 2 and Record 7 as we will see later.



4) I followed the same logic with Record 1 that is parent of Record 2.
5) Having completed the “Middle to Top” path, I started symmetrically copying the same looping from “Middle to Bottom” to get the entire structure which will look like:



6) At end do not forget to add 1 root delimited record (Root) above Level1_8 that will occur as Single.

7) The last important thing is to set up the properties of the “Schema” node as below: