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 Tuesday, August 17, 2010 11:25 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: 2 days ago @ 9:08 AM
Points: 1,510, Visits: 8,472
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

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 #970631
Posted Monday, August 23, 2010 8:34 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: 2 days ago @ 9:08 AM
Points: 1,510, Visits: 8,472

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

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 #973486
Posted Tuesday, August 24, 2010 10:02 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, December 23, 2014 6:28 AM
Points: 252, Visits: 1,768
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.
Post #974250
Posted Tuesday, August 24, 2010 10:10 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: 2 days ago @ 9:08 AM
Points: 1,510, Visits: 8,472
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

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 #974263
Posted Wednesday, August 25, 2010 6:00 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Sunday, September 14, 2014 3:17 PM
Points: 1,118, Visits: 2,224
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/

Post #975281
Posted Wednesday, August 25, 2010 6:06 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: 2 days ago @ 9:08 AM
Points: 1,510, Visits: 8,472
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

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 #975286
Posted Wednesday, August 25, 2010 6:17 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Sunday, September 14, 2014 3:17 PM
Points: 1,118, Visits: 2,224
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/

Post #975289
Posted Wednesday, August 25, 2010 6:23 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: 2 days ago @ 9:08 AM
Points: 1,510, Visits: 8,472

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

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 #975290
Posted Friday, February 25, 2011 5:00 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Monday, December 15, 2014 7:45 AM
Points: 395, Visits: 89
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?
Post #1069942
Posted Tuesday, March 1, 2011 6:33 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, December 23, 2014 6:28 AM
Points: 252, Visits: 1,768
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.
Post #1071177
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse