Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

import HL7 flat file to sql server by using ssis

Posted on 2010-08-20
16
Medium Priority
?
5,753 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
ID: 33486920
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
ID: 33487178
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
ID: 33487203
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
Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

 
LVL 16

Expert Comment

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

Author Comment

by:frankj168
ID: 33487571
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
ID: 33488033
Yes,It sure is.. what's your table structure?
0
 

Author Comment

by:frankj168
ID: 33488289
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
 
LVL 16

Expert Comment

by:vdr1620
ID: 33489039
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
ID: 33489343
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
ID: 33489473
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
ID: 33489942
Not a problem.. Post the solution here..So that others can use it
0
 

Author Comment

by:frankj168
ID: 33490638
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 1000 total points
ID: 33492232
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 1000 total points
ID: 34109452
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
ID: 34505792
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

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Viewers will learn how the fundamental information of how to create a table.

926 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