Providing SQL access to flat file (proprietary) database

Question

We have a "database" that stores historical data in what could effectively be called a highly compressed proprietary "flat file" format.  Typically these are accessed and queried using some C++ code that accesses the files directly.
We would like to provide access to the data to a third party who would like to use SQL queries (e.g. MSSQL).
Are there tools, libraries, or software packages that we can use to present an interface, ideally something that could be connected to using MS SQL Server Management Studio to develop queries that when run, run against our flat file data (short of importing the data into a SQL database).
I have heard rumors of such tools but haven't seen anything yet in practice.

Piotr · Answer

Possible solutions:

Use SSIS package registered as a view.
Downside of this option is that any where conditions are not pushed down to SSIS, so dotNet will have to read all the data. Of course you can create multiple views - like last day only, aggregated values, etc.
Walkthrough: Publish an SSIS Package as a SQL View
Use a CLR Table-Valued Functions
This way you can pass parameters to underlying dotNet code
Use PolyBase.
This would require a lot of added infrastructure and will allow you to split processing the file to multiple servers (or in Azure).

On the other hand I don't think that compression you are using will be much better than columnstore compression, so I would advise to reevaluate storing this in SQL Server itself.

Providing SQL access to flat file (proprietary) database

One Answer

Add your own answers!

Ask a Question