import HL7 flat file to sql server by using ssis
Posted on 2010-08-20
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,
Btw, The HL7 messages are in pipe-delimited text - see below.