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

  • 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!

  • 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.

  • 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!

  • 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.

  • 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!

  • 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.

  • 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

  • thank you very much, I will check it out!

    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