Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12

Lookin for Reference for EDI Using SSIS and X12 Format Files Expand / Collapse
Author
Message
Posted Wednesday, March 2, 2011 10:38 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Monday, September 29, 2014 10:25 PM
Points: 254, Visits: 755
Alvin-

Not sure where you're at in your progress, but in our processing of 834/835 files, we use home-grown parser apps to break the files down into more of a flat-file layout, which makes them easier for actual data processing (they're each slightly modified/tweaked from the x12 specs in order to be better used from what our partners send us and for what our business needs are).

Using them in conjunction with SSIS, I'm passing raw EDI files through the parser via a ExecProcess task, and then using the resulting flat file in the data flows. Some raw files do end up needing a little pre-parsing using some regex in a script component, but it at least gets the job done. Again, this is nice if you already have some stand-alone parsing apps.

Of course, with the upcoming 5010 transaction set (to be implemented by 1/1/12), we'll be taking a fresh look at how we handle these files ... it'd be nice to eventually work our parsing logic into a custom component without a 3rd-party product, but I think that'll be a warm weather project :)

(I'll keep my eyes on this thread ...)
Post #1072071
Posted Wednesday, March 2, 2011 10:50 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Today @ 7:33 AM
Points: 7, Visits: 117
Would it be possible to encapsulate the file and rows within XML tags, and then create an XML schema that would determine how to parse each row type?
Post #1072080
Posted Thursday, March 3, 2011 3:56 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Monday, September 29, 2014 10:25 PM
Points: 254, Visits: 755
I'm not familiar with the 850 file spec, but x12 834/835s are essentially a single line when the raw file is viewed ... I'm guessing that an x12 850 is similar? In it are effectively tagged loops at varying levels, similar to an xml, so I don't think enclosing that data in a higher-level xml tag will do much more to help, but I could be wrong. If you're approaching it via an xml schema, I'd think that you'd just want to use the loops/tags that are already in the raw data?
Post #1072992
Posted Monday, March 11, 2013 2:26 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, December 3, 2014 9:53 AM
Points: 132, Visits: 341
Any updates? We're looking to do some simple modifications to an 837 file. I just need to replace the billing name/address and append an invoice# in a specific segment.
Post #1429475
Posted Tuesday, March 12, 2013 7:59 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 9:09 AM
Points: 4,471, Visits: 6,400
lduvall (3/11/2013)
Any updates? We're looking to do some simple modifications to an 837 file. I just need to replace the billing name/address and append an invoice# in a specific segment.


I have been doing healthcare EDI processing for almost a decade now and I can tell you there is no "simple modifications" for an 837 file!! The 005010X222A1 Consolidated Guide that controls the document layout and usage is about 600 pages long!! Many segment/element identifiers can be found in different places throughout a transaction so you always have to track exactly where you are in the hierarchy. You can build your own parser that will keep track of where you are so you can navigate to the CORRECT address and CORRECT location to insert the REF with your invoice#. Beware the looping nature of the document.

I can say with complete confidence that SSIS is NOT the tool to use for what you wish to do. Best would be to already have a third-party product that can consume/process the 837 for you and use that. Failing that, you probably have to make your own parser using simple text file processing and the language of your choice. The parsing part isn't difficult really - it is all the looping and situational stuff that will drive you crazy. Oh, and TEST EVERY PERMUTATION!!

Best of luck with it! Feel free to drop me a PM if you need some professional assistance.


Best,

Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru at GMail
Post #1429827
Posted Saturday, July 27, 2013 9:18 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, August 13, 2013 10:52 AM
Points: 7, Visits: 13
I have been doing healthcare EDI processing for a mere eight years, but that's long enough to say that not all such work is complicated. I will agree that you shouldn't attempt even the simple ones without some familiarity with the IG.

I was once tasked to do something similar to what he asked. I did not use SSIS, because it was only one file per day. If there were many files, I would drop the code into a script component and put a foreach task around it.

Instead of looping though segments and having to keep track of the entire complicated structure, I inserted markers at easy-to-find points of interest (beginning of a claim, SE segment, etc.). SPLIT the entire file on the markers, and each piece can be manipulated without having to keep track of overall file structure (other than counting any segments added or removed, to update the next SE). JOIN the updated pieces back into a single string, and write it to the output file.
Post #1478286
Posted Sunday, August 31, 2014 1:31 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, September 3, 2014 7:00 AM
Points: 46, Visits: 99
Hi Alvin,

I have one requirement for process 835 files and loaded into database, Can you share your experience.

Thanks,
Post #1609170
Posted Thursday, October 16, 2014 2:45 PM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Yesterday @ 5:13 PM
Points: 456, Visits: 1,060
I can add that SSIS is extendable. Thus it is possible to create in .NET your very own data source component for the data flow task to adapt to files in non standard formats. To do this well seems to me complicated but there may already be someone that has. I'm sure that is what CozyRoc is pointing to.
Post #1626376
Posted Thursday, October 16, 2014 3:37 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Yesterday @ 10:51 AM
Points: 1,510, Visits: 8,470
venkat113 (8/31/2014)
Hi Alvin,

I have one requirement for process 835 files and loaded into database, Can you share your experience.

Thanks,


It's doable in SSIS, but it's a lot of work and I would only try to process X12 data in a data flow if I didn't have any good options.

As mentioned in the post above, using a ".NET" data source, you can do almost anything. What took the most time to figure out was how to pass a value from one line to another line. That might have been the key item that dictated using a .NET source.

Doable? Yes.

Recommended as best option? No.

FYI, I never did complete the process. I was only trying to prove that it could be done.




Alvin Ramard
Memphis PASS Chapter

All my SSC forum answers come with a money back guarantee. If you didn't like the answer then I'll gladly refund what you paid for it.
Post #1626387
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse