SSIS parsing of an irregular flat file?

Question

I'm pretty familiar with SSIS parsing of regular delimited text data files, however, I'm looking for some advice on an approach to tackle a file that looks like this test file:

ISA*00* *00* *01*220220220 *ZZ*RL CODE 01*060327*1212*U*00300*000008859*0*P*:~
GS*RA*CPA-BPT*LOCALUTILITY*060319*1212*970819003*X*003030~
ST*820*000000001~
BPR*C*321.91*C*X12*CBC*04*000300488**9918939***04*000300002**1598564*070319~
TRN*1*00075319970819105029~
REF*RR*0003199708190000174858~
DTM*097*070318~
DTM*107*070318~
N1*PR*DIRECT PAYMENT~
N1*PE*ABC CORPORATE BILLER*ZZ*90005836~
ENT*1~
N1*PR*BILLING - TEST - NATTRASS~
RMR*CR*0009381082105011**142.15~
REF*TN*000303965~
DTM*109*070316~
ENT*2~
N1*PR*BILL FREID TEST~
RMR*CR*0011010451800011**179.76~
REF*TN*000304189~

The 321.91 is the total of the transaction.

I would prefer to do this with SSIS, but could also do create a C# parser.

Suggestions would be appreciated.

Thank you.

Answer 1

This sample file would be to complicated for a regular flat file data source component to handle. The only alternative to do this using SSIS would be to use the script component as a source in the data flow. You would then have access to all of the .Net Framework libraries and you would then be able to create your customized c# or vb (using 2008) or vb (using 2005) parser within that component. Good Luck!

Answer 2

There is no easy answer to this. SSIS' Flat File data source does not handle anything but the simplest formats. I've used the flat file task to read in ragged right or reccord typed flat files as one big column. I'd then run it through a script task to handle the parsing. Sorry there is no easier answer than that... you'd think there would be.

Answer 3

Assuming doing this in SSIS is not a clean option, you could use the open source File Helpers library in order to efficiently write a C# parser.

File Helpers are useful for parsing delimited files as well as fixed length files.

Answer 4

The file shown above is called a "837 File". You can search for 837 file parsers or ways to parse such file in c#. I don't have a link to that but at least this should at least help you find solution (remember: "837 File")

( By the way, I believe this is a violation of HIPAA rules. You cannot publish/post this in public. Although the data might be fictitious, you cannot really post something like this. Please double check that )

03/18/10 UPDATE:

I found a "How To Read the ANSI 837 File" doc here (PDF Download)

03/26/10 UPDATE:

I found more information on this. The example might not be an 837 file at all. It appears that it is an ANSI X12 - an EDI standard. Please take a look at EDI Mapping doc (pdf)

and, also this Google Search Result on EDI X12

And, this EDI Tools for Programmer

Please post solution to this problem so anybody who needs the answer will benefit from it ;-)