BizTalk: Using the Flat File Mapping Wizard to map a NACHA financial file

If you've ever had to work with any financial ACH transactions (Automated Clearing House, think direct deposit...) then chances are you've run into a NACHA file. These are flat, positional files composed of repeating 94 character records. The basic structure looks like this:

File Header Record
   Batch Header Record
      Entry Detail Record
      Entry Detail Record
      Entry Detail Addenda Record (optional)
   Batch Control Total
   Batch Header Record
      Entry Detail Record
   Batch Control Total
File Control Record

Notice that you can have one or more batches in a file (everything from Batch Header to Batch Control), each having one or more Detail Records. All rows are required except for the Addenda Record.

A sample file might look something like this:

101 065000090199999999903122908001094101CAPITALONE BANK        DEMO COMPANY                  
5200DEMO COMPANY 2  PAYROLL             1999999999PPDPAYROLL   031231031231   1065000090000001
622065000090123456789        00000102001112223336     MARY JONES              0065000090000002
622111901014987654321        00000890774445556666     JACK STEVENS            0065000090000003
820000000200241901190000000000000000000992771999999999                         065000090000001
5200DEMO COMPANY 3  PAYROLL             1999999999PPDPAYROLL   031231031231   1065000090000001
6220650000901111111111       00000319181234567890     PAUL SMITH              0065000090000001
820000000100241901190000000000000000000319181999999999                         065000090000001
9000001000001000000030024190119000000000000000000131195                                      

A quick Google search should produce several implementation guides for this file structure.

Since this file has a header (File Header Record) and trailer (File Control Record) with a repeating body, we can use the Flat File Schema Wizard to create header, trailer and body schemas that will be used in a Flat File Disassembler receive pipeline component to de-batch the file into individual messages.

Start by creating a new solution, and firing up the Flat File Schema Wizard. Name your schema NACHA_Header. On the Flat File Schema Information page, click Browse and use the sample data above as your input file. Change the Record Name to "Header" and click Next.

On the Select Document Data page, highlight the first line of data including the new-line characters {CR} and {LF} as shown:

And click Next.

On the Select Record Format, leave the default (By delimiter symbol) selected and click Next.

On the Delimited Record page, check "Record has a tag identifier" and enter 1 for the Tag. Click Next.

On the Child Elements page, change the Element Name to FileHeader, and the Element Type to Record:

And Click Next. You'll be taken to the Schema View window and see the FileHeader node underneath Header. Click Next to start setting up the individual fields.

On the Select Document Data page, leave the default selected (it should skip the first "1" and end before the CR-LF) and click Next. On the next page (Select Record Format), choose "By relative positions" and click Next.

Now you're going to specify the starting positions of each element. You'll need the implementation guide for this, obviously. Left-click at the start of each field, but keep in mind that since this record has a tag identifier, it's not shown on screen, so you'll need to adjust the column number in which you're clicking. Also, most implementation guides will begin a position 1, while this screen is 0-based. For example, the Priority Code column starts at position 02 in the guide, but at position 0 on screen. Just subtract 2 from everything and you should be ok.

When complete, click Next.

On the Child Elements screen, give each element a descriptive name, but keep in mind that the Record Type Code element ("1") will not be listed since you marked it as a Tag identifier:

Click Next, then Finish on the Schema View screen. The header schema is now complete.

Repeat the same steps for the trailer (the row beginning with "9") and call it NACHA_Trailer.xsd. It should look something like this:

Now we'll create the body schema. Launch the schema wizard and create NACHA_Body.xsd. Browse to the same instance file, use "batch" as the Record name and click Next.

On the Select Document Data screen, you're only going to select the first repeating batch, like this:

Click Next to go to the Select Record Format screen, keep the default (By delimiter symbol) and click Next.

Since every new batch begins with a Batch Header row, and that row has a "5" for the Record Type Code, click "Record has a tag identifier" and enter "5" for the Tag. Click Next.

Now you'll define the Child Elements. They are all of the Record element type, but remember that Detail records can repeat. So the first one should be a "Repeating record" and the second "Ignore", like this:

And click Next. You'll be on the Schema View page, and see three records under the Batch node. Click Next to begin editing BatchHeader. The SelectDocumentData screen should look similar to when you were creating the header, with everything AFTER the "5" selected. Click Next. On the Select Record Format screen, choose "By relative positions" and click Next. On the Positional Record screen, set your starting positions and click Next. Give each element a descriptive name on the Child Elements screen (remember that the Record Type Code won't be shown!) and click Next. You'll be ready to start setting up the Detail row, so click Next.

The Detail row will be set up almost the same as everything else you've done to this point, EXCEPT that you'll need to specify a Tag identifier of 6.

Since you're doing this on an individual record, you'll actually have to set the positions for the Record Type Code, like this:

Finish up the BatchControl row, and your Schema view should look like this:

click Finish and save everything.

You'll need to make a modification to the body schema, so in the schema view click the <Schema> node. In the Properties pane, change Element FormDefault to Qualified. This indicates that locally declared elements must be qualified by the target namespace in an instance document.

Now let's test it. Since we're using 3 schemas to disassemble the flat file, you can't just right-click and Validate Instance, you actually have to go through a pipeline. For now we'll just FFDasm.exe to test. Open up a command propmt, change to your project directory, and type:

"C:\Program Files\Microsoft BizTalk Server 2010\SDK\Utilities\PipelineTools\FFDASM" SampleNachaFile2.txt  -hs NACHA_Header.xsd -bs NACHA_Body.xsd -ts NACHA_Trailer.xsd

(Of course, your path names could be different)
If everything has been set up correctly, you should get two xml files that look something like this:

In the next post, I'll go through setting up the actual pipeline and preserving the header information received on the file (notice that your message doesn't include the header or footer), as well as creating an outbound file.

Technorati Tags:

Print | posted @ Thursday, April 21, 2011 2:54 PM

Comments on this entry:

Gravatar # re: BizTalk: Using the Flat File Mapping Wizard to map a NACHA financial file
by Nami at 4/20/2012 6:10 AM

Please let me know the link to the next post ... i.e."setting up the actual pipeline and preserving the header information " ....
Gravatar # re: BizTalk: Using the Flat File Mapping Wizard to map a NACHA financial file
by Bill at 4/20/2012 2:52 PM

Sorry, I totally forgot to write the follow-up post! Here it is:

http://geekswithblogs.net/bosuch/archive/2012/04/20/biztalkndashmapping-a-nacha-file-using-the-flat-file-mapping-wizard.aspx
Gravatar # re: BizTalk: Using the Flat File Mapping Wizard to map a NACHA financial file
by Nami at 4/20/2012 3:39 PM

Thanks for the link....
Gravatar # re: BizTalk: Using the Flat File Mapping Wizard to map a NACHA financial file
by Steve Young at 11/6/2012 6:03 PM

Thank you very much for this tutorial. It's very helpful. I am new to BizTalk and can't get the FFDASM to work. Is there any chance you can attach a sample project?
Post A Comment
Title:
Name:
Email:
Comment:
Verification: