import csv data stored in a blob column

Question

Here's what i need to do:

1. Users will upload CSV files into SQL Server 2012 blob column
2. Each night, i would like to take each file and import the data into table
3. I will have 2 web sites using the same database. One is internal and one is in the DMZ
4. If i want my 2 sites to see the files, the easy way would be to store the files in the database. It will be also more easy to take backup of everything
5. If i store the files on a shared folder, i will have to open a port in the DMZ to see this shared folder inside our network. I don't know if its a good idea. Also, the backup plan will be harder to make (backup the database and backup the files separatly and try to synchronize them)

Is it possible to use a stored procedure to read the file inside the blob column, loop on each line and insert the data in a table?

1. Should i export the file to a temporary system file
2. Read the file and do the importation

or can i

1. Read the file directly from the blob colum and do the importation

other proposition?

Answer 1

Is it possible to use a stored procedure to read the file inside the blob column, loop on each line and insert the data in a table?

Definitely.

However, I would consider rethinking this plan.

A SQL Server database generally isn't a great place to be storing BLOBs, particularly given that you're just going to turn around and process them into row data later. It's a lot of extra disk activity and (presumably more expensive) storage that you just don't need to use. Also, the kind of processing being proposed will almost certainly perform worse than the many direct-processing alternatives. Generally speaking, the less processing you need to do (and the simpler the process itself), the better performance you'll get out of the system. And it will probably be more reliable as well.

Why not turn the files into row data immediately? Are you concerned about blocking or latency of the client application? If that's the only concern, consider setting up an asynchronous queuing system, possibly by using Service Broker. You can use BULK INSERT to turn the CSV files into row data directly from the file system without first loading the files as BLOBs. If this is going to blast the CPU during load when you need to run other things on the same server, consider using Resource Governor if you're on Enterprise Edition.

If you have to process the files in batch at night due to other constraints, it may be better to simply direct the raw files into a named (YYYYMMDD) folder on a network share during the day, and then once/day use an SSIS package with a Foreach File Enumerator container to process the files. I suppose this could also work in a job-based scenario where you just fire it up every 15 minutes or so to process and remove files that landed in the folder in the last period. SSIS may also be a good solution if you need some kind of transformation process to happen between the raw files and the row data.

There are lots of different possibilities here depending on your exact requirements, but I think I've given enough of the more common elements that you can piece together a solution that will work best for your situation.