keith.westberg (3/21/2014)
I apologize for not laying the groundwork... so here's the process.User uploads a file to website application. The site parses the text file and generates insert parameters for the called sproc on sql server 2008 r2. This process is repeated for every line found in text file besides the header line which is parsed for appropriate columns. Before the call is made to the db, the business logic scrubs the content to assure data quality and format. Now some text files can be 100k lines, in which case this app makes 100k db calls.
My search is for a way for the user to simply upload the file to the db directly and kick off a process which would take place only on the db that walks the file line by line. Our users are NOT granted access to an area to drop files off for bulk loading. And the server admins will NOT provide a location for the db to reach out to use file system for its data source.
The only option i can see with these limitations, is to allow the user to push the entire file to the database and work the process from there. Granted it would not be the sexiest thing on the block... but its worth a run.
Any tips or sources for like process would be amazing... thank you
This won't help except, maybe, to know that you have someone that agrees with you but I just have to say that I'm totally amazed at some DBAs/Server Admins . Yes, DBAs/Server Admins absolutely need to be extremely concerned about security, size, and safety but DBAs/Server Admins aren't supposed to be roadblocks. They're (we're) supposed to be enablers of safely doing things the right way and uploading a file and then bulk loading it is absolutely the correct method for doing this rather than sending a 100k row file across the pipe one bloody row at a time. The system(s) will pay dearly when just a handful of users try to upload such files in the RBAR fashion that you've been constrained to. The web Server is going to take a beating, as well.
There's absolutely no reason why an uploadable FTPS or similar site couldn't be constructed and offered to the users with logins and passwords that would preclude users from seeing each other's files.
Maybe there are some mitigating factors that I'm not aware of but, from here, it seems that the DBAs/Server Admins have erected a totally unjustifiable roadblock for what appears to be an important process. I don't know if it's arrogance, ignorance, or slothfulness that has cause them to do this to you but they need to realize that you're all working for the same company and when it comes to handling data, they need to be enablers instead of nay sayers because they're the ones that know how to do this in a secure fashion and with performance in mind.
I apologize for the rant but this kind of stuff just bugs the hell out of me.
Shifting gears to the problem at hand, you said...
User uploads a file to website application.
What is the process for that happening? Is the file loaded onto the Website box itself and then the app reads from that file or ???
--Jeff Moden
Change is inevitable... Change for the better is not.