Lookin for Reference for EDI Using SSIS and X12 Format Files
-
Let me start by saying that I'm not looking for assistance in solving a work problem. I'm looking to improve my knowledge of how to do thing using SSIS, and I'm hoping to turn this into a PASS Chapter presentation, and possibly into an article also.
Iām wondering if anyone has found a good (or any) reference for doing EDI with SSIS using X12 format files.
When I first started thinking about this it sounded pretty simple. I'm trying to develop a methodology that would keep most, if not all, of the processing inside a SSIS DataFlow. For example, if I'm processing purchase orders (X12-850 format), I'd like to have the SSIS package writing the header and detail records directly to the DB tables without having to temporarily storing the individual segment then having a separate process creating the header and detail records from the temporary storage. It turns out that accomplishing this is not as easy as it looked at first.
I know at least one way to accomplish this. I'm just wondering if anyone has published anything on this topic that would give me some new options to consider. None of my searches have come up with anything worthwhile.
Thanks in advance.
Alvin Ramard
Memphis PASS Chapter[/url]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[/url]
-
No replies so I'm slowly coming to the conclusion that there is no such reference available, or if there is one it is not easy to find.
Looks like I may have to write my own.
Alvin Ramard
Memphis PASS Chapter[/url]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[/url]
-
When I went looking for tips on X12 processing last year, I wasn't able to find any resources out there. In my case, I was able to get the vendor to provide a delimited extract instead, but I'm still interested in the possibility of processing X12-837 and HL7 files via SSIS.
-
Dave23 (8/24/2010)
When I went looking for tips on X12 processing last year, I wasn't able to find any resources out there. In my case, I was able to get the vendor to provide a delimited extract instead, but I'm still interested in the possibility of processing X12-837 and HL7 files via SSIS.Thanks Dave,
I'm pretty sure I've figured out how to handle an 850 (Purchase Order) file completely with single pass through a DataFlow. All I need is the time to actually try it.
The same methodology should work with an 837. The main difference between an 850 and an 837 is the number of times you have to refer to information provided in an earlier segment.
It's been a few years since I last looked at HL7, and thank God I never had to use HL7. I'll have to look at that too.
I'll update this thread after I've tested my idea.
Alvin Ramard
Memphis PASS Chapter[/url]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[/url]
-
Hi Alvind,
As I understand you have found first-hand it is not easy to flatten or transform EDI data into tabular data flow format. There is a third-party commercial solution for SSIS called CozyRoc EDI Source, which has been available on the market for 2 years now. It has been already successfully used to process 210, 810, 812, 820, 822, 832, 834, 835, 850, 852, 855, D95B. I suspect 837 format can be handled as well. The link to the documentation I have sent you contains sample configuration and input file for 852 format. This could give you a pretty good understanding what is involved in the creation of specific configuration files.
---
SSIS Tasks Components Scripts Services | http://www.cozyroc.com/
-
CozyRoc (8/25/2010)
Hi Alvind,As I understand you have found first-hand it is not easy to flatten or transform EDI data into tabular data flow format. There is a third-party commercial solution for SSIS called CozyRoc EDI Source, which has been available on the market for 2 years now. It has been already successfully used to process 210, 810, 812, 820, 822, 832, 834, 835, 850, 852, 855, D95B. I suspect 837 format can be handled as well. The link to the documentation I have sent you contains sample configuration and input file for 852 format. This could give you a pretty good understanding what is involved in the creation of specific configuration files.
Thanks for the post.
I am aware of your 3rd party solution. I'm trying to come up with a good way of doing this without using 3rd party tools.
Alvin Ramard
Memphis PASS Chapter[/url]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[/url]
-
Thanks for the post.
I am aware of your 3rd party solution. I'm trying to come up with a good way of doing this without using 3rd party tools.
Alvin,
EDI is somewhat similar to XML in terms of the data structures it can represent and therefore tough nut to crack using only what is available in the standard SSIS box. I'm not saying it impossible, but the end result will be complex.
---
SSIS Tasks Components Scripts Services | http://www.cozyroc.com/ -
CozyRoc,
I understand what you're saying. I've had about 5 great idea how to do it but none of them would work.
I'm pretty sure my latest idea will work, but I know it will take more custom programming than your solution.
Alvin Ramard
Memphis PASS Chapter[/url]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[/url]
-
Were you able to figure out an easy way to import files in HL7 data format using out of the box SSIS? If you have can you share your experience?
-
In my case, that project was put on the back burner. Rather than forging ahead in SSIS though, I downloaded a C# port (NHAPI) of an open source Java application (HAPI) which has the capability to both create and parse HL7 messages. I haven't had the opportunity to examine it closely yet, but it looks promising for what I need.
-
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 ...) š
-
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?
-
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?
-
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.
-
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 on googles mail service
Viewing 15 posts - 1 through 15 (of 21 total)
You must be logged in to reply to this topic. Login to reply