July 27, 2013 at 9:18 am
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.
August 31, 2014 at 1:31 pm
Hi Alvin,
I have one requirement for process 835 files and loaded into database, Can you share your experience.
Thanks,
October 16, 2014 at 2:45 pm
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.
----------------------------------------------------
October 16, 2014 at 3:37 pm
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.
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]
December 10, 2015 at 11:32 am
For HL7 take a look at Mirthconnect.
July 25, 2017 at 10:42 am
I'm tasked with creating SSIS packages that import and export data to an ERP system as sales orders and invoices.
While I'm very familiar with using flat files in SSIS, my latest project requires me to consume and produce Ansi X12 files. Specifically, I need to import 850 (purchase orders) and export 810 (invoice) files, along with possibly needing to export a couple of others.
Along with the obligatory advice of "DON'T!!!", my research has turned up numerous tools with price tags that make them impossible to consider, and a couple of free/open-source solutions that work as pre/post-processing parsers, translating between X12 and various flat file formats. The ones I found are:
Neither of these have been updated in a couple of years. Has anyone tried them?
Does anyone know of a solution that is being actively developed? Or any other low/no-cost X12/SSIS solution?
July 25, 2017 at 12:57 pm
samp.silvercreek - Tuesday, July 25, 2017 10:42 AMI'm tasked with creating SSIS packages that import and export data to an ERP system as sales orders and invoices.While I'm very familiar with using flat files in SSIS, my latest project requires me to consume and produce Ansi X12 files. Specifically, I need to import 850 (purchase orders) and export 810 (invoice) files, along with possibly needing to export a couple of others.
Along with the obligatory advice of "DON'T!!!", my research has turned up numerous tools with price tags that make them impossible to consider, and a couple of free/open-source solutions that work as pre/post-processing parsers, translating between X12 and various flat file formats. The ones I found are:
Neither of these have been updated in a couple of years. Has anyone tried them?
Does anyone know of a solution that is being actively developed? Or any other low/no-cost X12/SSIS solution?
1) PLEASE don't post to 3-year old posts. This one definitely deserves it's own posting.
2) I have several thousand hours of my life invested in creating ANSI X12 data loads/extracts. So I am fully qualified to tell you that this is a CERTIFIED BITCH of an effort, at least for the healthcare related ones that I worked on. They are unbelievably complex, and the document that describes the 837I and 837P format are 600-700 pages each!! Back when I did this SSIS had almost no hope of being successful with this and there was no third-party application to do facilitate the effort.
3) IMHO, without a third-party product that has a package that KNOWS the definitions, loopings, nestings and other constructs you are facing a nearly insurmountable task. And make no bones about it, if you roll your own EVEN WITH A THIRD PARTY PRODUCT you are at a SUBSTANTIAL RISK of screwing it up. The number of test cases you need to create to make sure you cover every possible permutation of looping/nesting runs in the thousands for the two file types I mentioned (which I believe are the most complex of the X12 data sets).
4) You may think the cost is prohibitive. Before you really decide that you need to try to put a dollar figure on having bad data loads recurring throughout the life of your self-created loader.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
Viewing 7 posts - 16 through 21 (of 21 total)
You must be logged in to reply to this topic. Login to reply