Run SQL query on a binary file?

Question

My company has a legacy micro-simulation program that simulates a population and changes to that population over a period of years.

For each year, the program produces a binary file with a record for each individual that holds their characteristics (e.g., age, maritial status, income ... about 20 fields).

We currently have several utility programs that read these files and produce summary reports. Problem is that each time somebody wants a new report, a new utility program has to be written.

Changing the program so that the records are stored in a database instead of binary files is out of the question (I have asked ... several times). I have written a few programs that import the binary files into a database and then run queries on the tables I have created. The problem here is that it always takes longer to import the data and run the query than it does to run a utility program written in c++ that just read the records one by one and accumulate the desired data. Often the binary files contain over 30 million records and the import step alone takes forever.

So here is my question. Is there anything out there that would allow me to specify the structure of my binary file and then run SQL queries on the file? I think you can use ODBC to run queries on plain text files, but I've never seen anything like that for binary files.

If there isn't anything available, what are the steps I would need to take to build something that could run a query directly on my file? I understand this would probably be way beyond my ability, but it can't hurt to know where I would need to start.

Answer 1

OpenAccess is a toolkit that you can use to build ODBC or JDBC drivers for arbitrary systems. Disclaimer: I've not used it, and another division of my company sells it.

Answer 2

It's possible using SSIS: Loading Binary Files into SQL Server Using SSIS

This amight also be of interest: Reading and Writing Files in SQL Server using T-SQL