Solved

import HL7 flat file to sql server by using ssis

Posted on 2010-08-20
16
5,040 Views
Last Modified: 2013-11-10
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|%


0
Comment
Question by:frankj168
16 Comments
 
LVL 16

Expert Comment

by:vdr1620
Comment Utility
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
 
LVL 16

Expert Comment

by:vdr1620
Comment Utility
I forgot to mention that in the columns tab of CSV Source you will need to choose | as your column delimiter
0
 

Author Comment

by:frankj168
Comment Utility
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
 
LVL 16

Expert Comment

by:vdr1620
Comment Utility
I see that the file has variable columns.. Is that what you are referring to by saying Complex ?
0
 

Author Comment

by:frankj168
Comment Utility
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
 
LVL 16

Expert Comment

by:vdr1620
Comment Utility
Yes,It sure is.. what's your table structure?
0
 

Author Comment

by:frankj168
Comment Utility
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
6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

 
LVL 16

Expert Comment

by:vdr1620
Comment Utility
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
 
LVL 30

Expert Comment

by:Reza Rad
Comment Utility
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
 

Author Comment

by:frankj168
Comment Utility
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
 
LVL 16

Expert Comment

by:vdr1620
Comment Utility
Not a problem.. Post the solution here..So that others can use it
0
 

Author Comment

by:frankj168
Comment Utility
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
 
LVL 16

Assisted Solution

by:vdr1620
vdr1620 earned 250 total points
Comment Utility
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
 
LVL 21

Accepted Solution

by:
Alpesh Patel earned 250 total points
Comment Utility
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
 
LVL 22

Expert Comment

by:PedroCGD
Comment Utility
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

Featured Post

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Join & Write a Comment

Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

728 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now