none
How to interpret HL7 Messages in SSIS RRS feed

  • Question

  • Hello Folks,

    In Helathcare HIS systems there are HL7 messages in the below formats that needs to be addressed using SSIS tool.

    How to read the below HL7 Message data if those messages are available either in .csv/.txt/ raw file /table and push it SQL table.

    Requirement: HL7 Message(Source)->SSIS Integration(Read HL7 Message)->SQL DB table(Destination)

    How to handle if multiple pipe symbols ||| and tild ~ or cap ^ characters are seen in the source data through SSIS 

    HL7 Messages are used to transfer electronic data between disparate healthcare systems. Each HL7 message sends information about a particular event such as a patient admission.

    0x0D Marks the end of each segment.
    | Composite delimiter.
    ^ Sub-composite delimiter.
    & Sub-sub-composite delimiter.
    ~ Separates repeating fields.
    \ Escape character.

    Sample lines example 1:


    MSH|^~\&|CERNER||PriorityHealth||||ORU^R01|Q479004375T431430612|P|2.3|
    PID|||001677980||SMITH^CURTIS||19680219|M||||||||||929645156318|123456789|
    PD1||||1234567890^LAST^FIRST^M^^^^^NPI|
    OBR|1|341856649^HNAM_ORDERID|000002006326002362|648088^Basic Metabolic Panel|||20061122151600|||||||||1620^Hooker^Robert^L||||||20061122154733|||F|||||||||||20061122140000|
    OBX|1|NM|GLU^Glucose Lvl|59|mg/dL|65-99^65^99|L|||F|||20061122154733|

    Sample 2:

    Btw, The HL7 messages are in pipe-delimited text - see below. 
    MSH|^~\&|||||||ORU^R01|HP1162468890966732|P|2.3||||||8859/1
    PID|||01104211-001||Firstname Lastname
    PV1||I|^^NICU3&3&1
    OBR|||||||20061102060130
    OBX||NM|92^Resp^SDN|0|54|rpm
    OBX||NM|40^HR^SDN|0|139|bpm
    OBX||NM|4596^SpO2 l^SDN|0|87|%
    OBX||NM|96^awRR^SDN|0|60|rpm
    OBX||NM|212^TV^SDN|0|8|ml
    OBX||NM|148^FIO2^SDN|0|0.43|
    OBX||NM|44^Pulse^SDN|0|138|bpm
    OBX||NM|1144^Raw^SDN|4|71|cmH2O/l/s
    OBX||NM|1748^Leak^SDN|4|0|%
    OBX||NM|1168^SpMV^SDN|4|0|%

    Please suggest the steps to achieve the same

    Thanks,

    Mahesh


    Tuesday, April 4, 2017 12:39 PM

All replies

  • Although it can technically be done, SSIS is not the best option for doing that kind of parsing because of the variable format in the table, optional parameters, etc.

    You should consider another option.

    Tuesday, April 4, 2017 12:45 PM
  • Hi Manesh,

    Cozyroc has SSIS EDI Source Adapter:

    EDI Source

    • Proposed as answer by COZYROC Wednesday, April 5, 2017 1:40 AM
    Tuesday, April 4, 2017 5:53 PM
  • Hi Mahesh,

    As the former replies stated, it is difficult to parse HL7 message directly in SSIS, you can try to use third-part tool to parse the HL7 message 1st, then load the data into SQL Server table.

    One way is that parse the HL7 message to XML file, please refer to: Transforming Messages: HL7 to XML

    Regards,

    Pirlo Zhang


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.



    Wednesday, April 5, 2017 10:17 AM
    Moderator
  • All,

    I referred the below link

    https://www.codeproject.com/Articles/29670/Converting-HL-to-XML


    The above will convert from HL7 to xml however am getting error as shown below:

    TITLE: Package Validation Error
    ------------------------------

    Package Validation Error

    ------------------------------
    ADDITIONAL INFORMATION:

    Error at Data Flow Task [Script Component [75]]: Microsoft.SqlServer.Dts.Pipeline.CannotCreateUserComponentException: Cannot create user component class. Make sure there is one class marked with SSISScriptComponentEntryPointAttribute in your script.
       at Microsoft.SqlServer.Dts.Pipeline.VSTAComponentScriptingEngine.CreateScriptObjectHelper(Assembly scriptAssembly)
       at Microsoft.SqlServer.Dts.Pipeline.VSTAComponentScriptingEngine.GetScriptEntryPoint(String versionGuid)
       at Microsoft.SqlServer.Dts.Pipeline.ScriptComponentHost.CreateUserScriptInstance()
    Error at Data Flow Task [Script Component [75]]: Microsoft.SqlServer.Dts.Pipeline.CannotCreateUserComponentException: Cannot create user component class. Make sure there is one class marked with SSISScriptComponentEntryPointAttribute in your script.
       at Microsoft.SqlServer.Dts.Pipeline.VSTAComponentScriptingEngine.CreateScriptObjectHelper(Assembly scriptAssembly)
       at Microsoft.SqlServer.Dts.Pipeline.VSTAComponentScriptingEngine.GetScriptEntryPoint(String versionGuid)
       at Microsoft.SqlServer.Dts.Pipeline.ScriptComponentHost.CreateUserScriptInstance()
    Error at Data Flow Task [Script Component [75]]: Microsoft.SqlServer.Dts.Pipeline.CannotCreateUserComponentException: Cannot create user component class. Make sure there is one class marked with SSISScriptComponentEntryPointAttribute in your script.
       at Microsoft.SqlServer.Dts.Pipeline.VSTAComponentScriptingEngine.CreateScriptObjectHelper(Assembly scriptAssembly)
       at Microsoft.SqlServer.Dts.Pipeline.VSTAComponentScriptingEngine.GetScriptEntryPoint(String versionGuid)
       at Microsoft.SqlServer.Dts.Pipeline.ScriptComponentHost.CreateUserScriptInstance()
    Error at Data Flow Task [SSIS.Pipeline]: "Script Component" failed validation and returned validation status "VS_ISBROKEN".

    Error at Data Flow Task [SSIS.Pipeline]: One or more component failed validation.

    Error at Data Flow Task: There were errors during task validation.

     (Microsoft.DataTransformationServices.VsIntegration)

    ------------------------------
    BUTTONS:

    OK
    ------------------------------
    Microsoft.SqlServer.Dts.Pipeline assembly don't exist in my library

    How to add the dlls  from GAC util,

    HL7 to XML (Not sure how best the resultant output would be)

    Please share the steps if any 

    Thanks,

    Mahesh

     


    Wednesday, April 5, 2017 1:06 PM