Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Lookin for Reference for EDI Using SSIS and X12 Format Files


Lookin for Reference for EDI Using SSIS and X12 Format Files

Author
Message
dg227
dg227
SSC Veteran
SSC Veteran (268 reputation)SSC Veteran (268 reputation)SSC Veteran (268 reputation)SSC Veteran (268 reputation)SSC Veteran (268 reputation)SSC Veteran (268 reputation)SSC Veteran (268 reputation)SSC Veteran (268 reputation)

Group: General Forum Members
Points: 268 Visits: 808
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 Smile

(I'll keep my eyes on this thread ...) :-D
belldb
belldb
Forum Newbie
Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)

Group: General Forum Members
Points: 9 Visits: 167
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?
dg227
dg227
SSC Veteran
SSC Veteran (268 reputation)SSC Veteran (268 reputation)SSC Veteran (268 reputation)SSC Veteran (268 reputation)SSC Veteran (268 reputation)SSC Veteran (268 reputation)SSC Veteran (268 reputation)SSC Veteran (268 reputation)

Group: General Forum Members
Points: 268 Visits: 808
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?
lduvall
lduvall
SSC-Enthusiastic
SSC-Enthusiastic (162 reputation)SSC-Enthusiastic (162 reputation)SSC-Enthusiastic (162 reputation)SSC-Enthusiastic (162 reputation)SSC-Enthusiastic (162 reputation)SSC-Enthusiastic (162 reputation)SSC-Enthusiastic (162 reputation)SSC-Enthusiastic (162 reputation)

Group: General Forum Members
Points: 162 Visits: 396
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.
TheSQLGuru
TheSQLGuru
SSCertifiable
SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)

Group: General Forum Members
Points: 5964 Visits: 8313
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!! Blink 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
Wesley.Groleau
Wesley.Groleau
Forum Newbie
Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)

Group: General Forum Members
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.
venkat113
venkat113
SSC Rookie
SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)

Group: General Forum Members
Points: 46 Visits: 99
Hi Alvin,

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

Thanks,
MMartin1
MMartin1
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1102 Visits: 2015
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.

----------------------------------------------------
How to post forum questions to get the best help
Alvin Ramard
Alvin Ramard
SSCrazy
SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)

Group: General Forum Members
Points: 2676 Visits: 11590
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.

For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help
Tim Wolf
Tim Wolf
Grasshopper
Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)

Group: General Forum Members
Points: 14 Visits: 46
For HL7 take a look at Mirthconnect.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search