Parsing EDI 834 flat file with SSIS, I need to be able to use multiple delimiters

Question

Parsing EDI 834 flat file with SSIS, I need to be able to use multiple delimiters

SQLArnold

SSCommitted

Points: 1847
More actions
February 22, 2013 at 10:59 am

#394242

Hello All,
I am working at a small health plan and I am trying to parse a 834 flat file data into a table using SSIS. One of my problems is the data is separated by { and then abbreviated words to inform you of what type of data is coming next. Example ~AMT{
After ~AMT{ will be the copay amout for the memeber.
834 Benefit Enrollment and Maintenance
Functional Group ID: BE
~INS - Insured Benefit OR MEMBER LEVEL DETAIL
~REF - Reference Information : The REF segment is required to link the dependent(s) to the subscriber.PRIOR COVERAGE MONTHS
~DTP - Date or Time or Period
~NM1 - Individual or Organizational Name : Either NM1 or N1 will be included depending on whether an individual or organization is being specified. MEMBER EMPLOYER or RESPONSIBLE PERSON
~PER - Administrative Communications Contact
~N3 - Party Location
~N4 - Geographic Location
~DMG - Demographic Information
~LS - Loop Header
EC - Employment Class
~LS - Loop Header
~LX - Transaction Set Line Number
~N1 - Party Identification
~REF - Reference Information
~AMT - Monetary Amount Information : The AMT segment is used to record the total Flexible Spending Account contributions in the transaction set.
~AMT{C1 - CO-PAYMENT AMOUNT
~COB - COORDINATION OF BENEFITS
~LX - Transaction Set Line Number
~N1 - Party Identification
~DTP - Date or Time or Period : REPORTING CATEGORY DATE
~LE - Loop Trailer : ADDITIONAL REPORTING CATEGORIES LOOP TERMINATION
~SE - Transaction Set Trailer : SE is the last segment of each transaction set.
Here is an edited segment of what I am trying to enter into a table.
~INS{Y{18{001{43{A{C{{AC~REF{0F{A00000000~DTP{000{D8{yyyymmdd~NM1{IN{1{LastName
{FirstName{Initial~PER{IP{{HP{phonenumber{TE{phonenumber~N3{MONTECITO{streetaddress~N4{city
{state{zipcode{{CY{13~DMG{D8{DateOfBirth{GenderMorF{I{H~LS{2700~LX{1~N1{75{ACTION CODE~REF{ZZ{AC~LX{2~N1{75{RENEWAL DATE~DTP{007{D8{DOB~LE{2700
the total length of the enrollment segemts is 60,000 caractors so entering it into a table is not possible or not that I could do without cutting the file in half.
I would like to enter the data as such.
ActionCode 001 -- means maintenance
Planid A00000000
DOB mmddyyyy
etc.
but I would be okay with
col1 INS
col2 y
col3 18
col4 001
col5 43
col6 a
col7 c
col8 ac
etc.
I would appreciate any help pr assistance, I have searched Google and have not found a good solution.
Thanks inadvance and have a great day!
Cheers!
The pain of Discipline is far better than the pain of Regret!

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

edi834 SSC Enthusiast Points: 124 More actions · Answer 1

Hi, there are several 834 EDI file parsers out there. I developed a SQL SSIS package "file listener" that picks up the file and loads into a SQL database. If you need any help let me know.

SQLArnold SSCommitted Points: 1847 More actions · Answer 2

Hello,

Yes, any help would be great. I would like to do the samething. I would like to use SSIS to import EDI 834 files into a database. Any assistance would be greatly appreciated.

Thanks,

Tim Harms

The pain of Discipline is far better than the pain of Regret!

edi834 SSC Enthusiast Points: 124 More actions · Answer 3

In my case, the EDI parsing was done based on an open source project:

http://x12parser.codeplex.com/

It is an excellent program that allows you parse 834 files.

SQLArnold SSCommitted Points: 1847 More actions · Answer 4

Thanks for the link, I have downloaded it but how did you use this with SSIS to import the 834 files into SQL Server database?

The pain of Discipline is far better than the pain of Regret!

david1575 SSC Rookie Points: 43 More actions · Answer 5

The source code in codeplex allows you to load an edi file into a sql database. The functionality it is already there. I just use to code to call it from SSIS script component.

kirissa2000 Valued Member Points: 61 More actions · Answer 6

Helloooo

was just discussing this at work.. so here is a solution that would work to just load up some EDI, if you wire it up, or find some re-usable code, plugin.. etc..

great detailed example... hope this helps someone else wondering about SSIS and INBOUND EDI.

http://stackoverflow.com/questions/6231414/how-to-load-a-flat-file-with-header-and-detail-data-into-a-database-using-ssis-p

SQLArnold SSCommitted Points: 1847 More actions · Answer 7

thank you very much, I will check it out!

The pain of Discipline is far better than the pain of Regret!