HL7 message import to sql server

Question

Question

leo dec · Mar 8, 2019

#HL7 #Ensemble

hi

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

anyone who did it before please help..

Regards

Discussion (16)1

Log in or sign up to continue

Eduard Lebedyuk · Mar 11, 2019

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

0 0

Rich Taylor · Mar 13, 2019

Leo,

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

0 0

Rich Taylor · Mar 15, 2019

Leo,

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.

0 0

score 0 · Answer 1 · 2019-03-12T07:33:49-04:00

Hi

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

regards

score 0 · Answer 2 · 2019-03-12T12:22:02-04:00

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.

score 0 · Answer 3 · 2019-03-13T03:57:16-04:00

Hi

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.

regards

score 0 · Answer 4 · 2019-03-13T04:49:40-04:00

Eduard Lebedyuk · Mar 13, 2019

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

0 0

score 0 · Answer 5 · 2019-03-13T05:47:51-04:00

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:-

MSH|^~\&|||||20000211120000||ADT^A28|012345678|P|2.4
EVN||20000211120000
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..

score 0 · Answer 6 · 2019-03-13T06:53:08-04:00

hi

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

score 0 · Answer 7 · 2019-03-13T09:30:43-04:00

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.

score 0 · Answer 8 · 2019-03-13T10:30:23-04:00

hi

Is this course are you talking about?

https://learning.intersystems.com/course/view.php?name=Building%20and%20Managing%20HL7%20Productions

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

regards

score 0 · Answer 9 · 2019-03-13T10:53:06-04:00

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.

score 0 · Answer 10 · 2019-03-15T06:03:45-04:00

Hi

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.

Regards

score 0 · Answer 11 · 2019-03-15T08:17:58-04:00

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.

score 0 · Answer 12 · 2019-03-15T09:47:03-04:00

Eduard Lebedyuk · Mar 15, 2019

Or a Skype group.

0 0

score 0 · Answer 13 · 2019-03-15T10:49:36-04:00

leo dec · Mar 15, 2019

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

0 0