asked on

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

vdr1620

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

vdr1620

I forgot to mention that in the columns tab of CSV Source you will need to choose | as your column delimiter

frankj168

ASKER

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

vdr1620

I see that the file has variable columns.. Is that what you are referring to by saying Complex ?

frankj168

ASKER

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?

vdr1620

Yes,It sure is.. what's your table structure?

frankj168

ASKER

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.

vdr1620

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

Reza Rad

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.

frankj168

ASKER

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

vdr1620

Not a problem.. Post the solution here..So that others can use it

frankj168

ASKER

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

SOLUTION

vdr1620

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ASKER CERTIFIED SOLUTION

Alpesh Patel

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

PedroCGD

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.