leo dec · 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..


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

If you already have HL7 message inside Ensemble you can use  Caché SQL Gateway which provides access from Caché to external databases via JDBC and ODBC. You can use SQL Gateway (probably in ODBC mode) to update SQL Server table(s).


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


To import HL7 into Ensemble please refer to these guides.

Generally it could look like this: HL7  Virtual Document → Persistent storage → SQL GateWay → MS SQL Server.


Thanks will go through the guidelines... but does HL7 messages comes as single file in the column or data will be separated in each column.


You can separate a file into structured data using Virtual Document API.

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


I am using Ensemble where I can see the HL7 message (2.4) definition but as I am not familiar with libraries or C# programming... needs to use data services or sql server to extract the data into columns in sql table.

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.

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.


could you please give me direct online link from where I can start... whatever I could find is that how to build new HL7 message rather than retrieving data from HL7 message file and putting in columns in SQL Server.



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.

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

Hi, Rich! Email is private information - nobody sees your email, except you. But I connect you both if you want.

There is a request for PM in DC, vote for it if needed.

Or maybe we need to introduce Slack for DC.

for me email is the best as all social sites are blocked at my campus.