Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

import HL7 flat file to sql server by using ssis

Posted on 2010-08-20
16
Medium Priority
?
5,660 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

664 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