We are 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. Currently we are using SAS to parse and store the data. Is it feasble to skip SAS and instead use SQL Servers' ETL capabilites (DTS/SSIS) to import into a staging table in the SQL Server database? How difficult would this be? Please comment on the pros and cons of using DTS/SSIS versus 3rd party software for ETL for this relatively small and focused project. Please consider my limited IT background in your answer (physician/epidemiologist). Thanks
Solution: ETL for HL7 data into Microsoft SQL Server
Yes, its very possible. And much more efficient.
What is the format of the file you are parsing?
If you have SQL Server 2005, SSIS is much more efficient in how it handles and processes the data for import into SQL Server. It has excellent error checking possibilities.