Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question

1 Answer 1

up vote 1 down vote accepted

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.

share|improve this answer
    
the reason why i want to store the files in the database is to facilitate the backup plan and also because my web site can be installed in 2 differents server (one in the dmz and one internal). If i want my 2 websites to see the files, i need to open a share folder that my dmz will see and im not sure that it's a good idea. And like i said, the backup plan is simpler. What's you tought on this? –  Alexandre Jobin Apr 17 '13 at 21:22
    
@Alexandre: You didn't mention anything about needing to "see" the files, or about backups. I assumed it was a one-way process, and potentially one day of data loss was acceptable. Please edit the question to include all relevant details. This is why I'm not necessarily suggesting exact solutions, just giving you the possible tools that are normally used for this sort of thing. –  Jon Seigel Apr 17 '13 at 21:39
    
I've added more info about what i need to do. Sorry if it's wasnt clear at first. I appreciate your ideas and i will look at them closely. If you have something to add, don't hesitate! –  Alexandre Jobin Apr 18 '13 at 13:23
    
@Alexandre: One way or another, you have to poke a hole in the DMZ to send through the files or data. Either it's a file share, or it's a connection to a SQL Server. If the security of the file share is configured correctly, I don't see an issue with that (though TBH I'm not a network expert). In terms of backups, you could use something like DFS to replicate the files to an alternate location. Also, you could use NTFS compression to save storage space, something that isn't available out-of-the-box with SQL Server. –  Jon Seigel Apr 19 '13 at 2:24
    
In any event, I would still consider trying to process the files as they're landed, or by using a queue. Is there a specific reason why they all have to be loaded at night in a batch? –  Jon Seigel Apr 19 '13 at 2:25

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.