Batch File Upload Web App or ETL
I'm currently looking at custom developed Web App Solution that is experiencing performance issues re: month end and batch file uploads (CSV files).
The application which is a .NET 3.5 (MVC 1.0) Web App has a file upload feature for users to do month end batch uploads along with day to day CRUD activities, the Web App is freezing on Users whilst they do the upload and it's taking a considerable amount of time to process (not to mention data validation errors and batch fails).
The file size is growing considerably, from ~50K to ~500K rows per file per month end (total of 20 separate files).
I'm looking for ideas on how best to solve this, my initial gut feeling is that the file upload, data validation and ETL should be done via SSIS and abstracted away from the Web App completely, the remote users could upload the file to a Secure FTP location, have an SSIS package, pick up the file, process it (Staging DB if necessary, approval process) and then move it over to the Application specific DB (SQL Server 2008 R2).
My client is looking for a re-design, I want to explore both a DB option and any other Web Options, we can move to MVC3 and .NET 4.0 during the solution re-design, batch file processing like this via Web Apps and C# is wrong IMO. (I could be wrong!)
An SSIS solution with an asynchronous callback was used to solve this. HTTPS File Upload...