· Mar 8, 2019

HL7 message import to sql server


I need help to import the HL7 message file into sql server using SAP Data Services..

anyone who did it before please help..


Discussion (16)1
Log in or sign up to continue


thanks for replying... sorry I am not familiar with cache much... sorry If I misunderstood are you saying that HL7  message file is in the form of sql table which means that each column data in HL7 message file is in the separate column in sql table?

If my understanding is wrong then is there any HL7 Schema definition file same what we have when we render the data from XML files.?

Please help


sorry dear... I can't understand.

could you please point me to the steps require to put data in columns..

see below test HL7 message:-

PID|||0123994567^^^^^NH||TEST^PINK^""^^""||12345678|M|||QUEEN STREET^BLK FLAT1 KIRTI ST^ENGLAND^^A110BP^^P|||||2|||||||||||||""|N
PD1|||^12345/1^12345/1|1234567/123456^A BLUE^PURPLE

like after PID 01234567 show go as ID and test go as forename, pink go as surname..


First can I ask which InterSystems product you are working with?  Ensemble, Health Connect, and IRIS for Health all provide tools that make handling of HL7 much easier.  Further these are all interoperability platforms which provide tools for building these types of integrations efficiently and fast.

A further question is what are you intending to do with this HL7 message?  Just put the entire message file contents into SQL Server as a blob or are you looking to pull out specific data to put into columns in a SQL table?  From your further comments I believe it is the latter,  but clarity would help here.

For the moment I make an assumption that you are using either Ensemble or Health Connect and point you at some documentation that can help you.

Ensemble HL7 Version 2 Development Guide

Also some Learning Services links

Integration Architecture

Building Your First HL7 Production learning path

Hope this helps

Not to worry.  You don't need to do C# programming or deal with obscure libraries.  With Ensemble you can accomplish much of what you need to do without alot of programming. I would start with the "Build your first HL7 production" learning path to discover how to create an HL7 integration.  There is also Classroom training that you can sign up for.  Check our learning services site for class schedules.

To update SQL Server you would create a Business operation (which will require some coding using ObjectScript) that uses our SQL Outbound adapter to update SQL Server.  Here is a link to that documentation.  Using the SQL Outbound Adapter

Finally, if you need more direct assistance I would recommend engaging with the Sale Engineer assigned to your account.   We're always here to help get you moving in the right direction.


Is this course are you talking about?

I don't have any programming tool installed on my machine and not much experience with coding... I am business analyst who has been given the file to extract the data and put it into the sql server so that people can understand the data easily..


Yes that would be a good course. There is a date coming up on April 8th.  In the meantime the online learning can get you started.  You should be able to accomplish quite a bit without any programming. Where you will need to do some is in creating a message class to contain the extracted data that is to be sent to SQL Server and to create the actual SQL operation to update SQL server.  Neither is difficult to do.  I would definitely suggest engaging with your InterSystems Sales Engineer for guidance.


I would go to the "Building Your First HL7 Produciton" learning path in the links I sent earlier.  There are several learning resources listed here.  If you are in  hurry you can skip the introduction components and go direct to the "Integration Architecture" course.  Then follow along with the other courses in order.  I would recommend at least the:

  • HL7 I/O course
  • all three courses under the message router section
  • Data Transformation Basics
  • Practice building data transformations
  • the two courses under troubleshooting would be advisable too
  • Do the Final Exercise.

You can always go back  and review other courses as needed.   Also search our Learning Services area (Learning on the top bar) for other courses and presentations.

You can contact me directly (my email is in my profile)) if you want to take this offline.