import HL7 flat file to sql server by using ssis

I am hoping to create a MS SQL database to store HL7 data that is exported from medical devices (oximeters, ventilators). As I understand it, the HL7 data is exported from a local network server to the hospital's network as stream of text messages. My understanding is that the data stream is converted to text files by Cloverleaf and which are sent to us every 12 hours. How can we use SQL Servers' ETL capabilites (SSIS) to import into a staging table in the SQL Server database? How difficult would this be? I appreciate it very much if you can give me some samples.

Thanks a lot,

Frank

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


frankj168Asked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
Alpesh PatelConnect With a Mentor Assistant ConsultantCommented:
Hi,

HL7 file is not small and easy. However, it is complex. It has header and data in it.

So, to save it in SQL database you have to split it as requirement using SQL Script task.

As per my opinion it is better way to do. Because, I had worked on it.
0
 
vdr1620Commented:
it can be done easily using SSIS.. Check the below link..It will help you understand how to process a CSV file using SSIS
http://www.kodyaz.com/articles/import-csv-flat-file-into-sql-server-using-ssis-integration-services.aspx

Since you are saying that you need to process them every 12 hours you can make use of For each loop as below
http://www.sqlis.com/sqlis/post/Looping-over-files-with-the-Foreach-Loop.aspx

So it would basically be a Data Flow Task to import files inside a For Each Loop... You can Save your package and Deploy it and then Schedule the job in SSMS to run every 12 hours as needed
0
 
vdr1620Commented:
I forgot to mention that in the columns tab of CSV Source you will need to choose | as your column delimiter
0
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

 
frankj168Author Commented:
It's not a simple csv file. I used ssis before and I know how to use it.

The HL7 is very complex, and I need some help on how to import it to SQL Server.

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 advise if you have some real solutions.  Thanks a lot !!
0
 
vdr1620Commented:
I see that the file has variable columns.. Is that what you are referring to by saying Complex ?
0
 
frankj168Author Commented:
Each row is a different segment, for example first row is
MSH - message header segment
The MSH segment defines the intent, source, destination, and some specifics of the syntax of a message.
http://www.med.mun.ca/tedhoekman/medinfo/hl7/ch200124.htm

second row is
PID - patient identification segment

The PID segment is used by all applications as the primary means of communicating patient identification information. This segment contains permanent patient identifying and demographic information that, for the most part, is not likely to change frequently.
http://www.med.mun.ca/tedhoekman/medinfo/hl7/ch300056.htm#I10

I think it's very complex compare to simple csv file, right?
0
 
vdr1620Commented:
Yes,It sure is.. what's your table structure?
0
 
frankj168Author Commented:
I am flexible on the table structure. At least, the data can be imported to SQL DB. Any idea how to parse the HL7 file in SSIS? Please advise.
0
 
vdr1620Commented:
i have never dealt with HL7 before .. Google helped me know more about the structure of the file... My first thought was to use CSV file source with two columns of length and the 2nd with text (which would basically have rest of the column data).. Then use a conditional split based on the 1st column and then use a script task to parse them out differently.. But i don't think that will work, as it is complex than i have thought.. I would try to convert into an XML File and then use XML source to load them into sql server.. You might want to look at this link that describes how to convert and HL7 file to XML

http://www.codeproject.com/KB/XML/HL7ToXMLConverter.aspx 
0
 
Reza RadConsultant, TrainerCommented:
did you tried to use BCP to import flat file to sql server table ever?
http://www.simple-talk.com/sql/database-administration/creating-csv-files-using-bcp-and-stored-procedures/
the BCP can help when your data structure in source/ destination is dynamic.

0
 
frankj168Author Commented:
Hi vdr1620,

Thanks for your reply and info. Actually, I googled and found the HL7ToXMLConverter too. I did lots of research today and I will use SSIS to implement it anyway. I think I can do it with some customized code. I appreciate your time and reply on this.

Frank
0
 
vdr1620Commented:
Not a problem.. Post the solution here..So that others can use it
0
 
frankj168Author Commented:
Actually, I used the similar idea as your first thought and it works fine after lots of testing.

First I used the flat data source to split the content to two columns(first column is 3 character lengh and the 2nd with rest of the content). Second, I use the conditional split to define and check the first column, I created 3 cases. Third, I used Derived Columns for splitting columns for each cases. Forth, I have one OLE DB Destination(table) matched to each case. So the data have been inserted to different tables.
Hope it can help others!

Cheers!

Frank
0
 
vdr1620Connect With a Mentor Commented:
The HL7 file on the web seemed to be more complex than the sample you mentioned,So i thought that method wouldn't be of much help...Glad, it worked for you
i would suggest using a script task to split the columns as its much faster compared  to derived column with a substring function
0
 
PedroCGDCommented:
This question has been classified as abandoned and is being closed as part of the Cleanup Program.  See my comment at the end of the question for more details.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.