Hello,
We have a project as described below.
The user uploads Excel or XML files into a SharePoint library. Then the information is exctracted from those files and conveyed into a MS SQL Server database for reporting purposes.
Since users upload very big files simultaniously we use a timer job that does transfering at night time. So users can see their new data in reports only next morning. This is not good. So can you suggest some other variants for the user to see his or her data in reports as soon as possible with minimum performance affection? A workflow? A console application?
Thanks.
If I were you, I would use something like Quartz.net to schedule a job to check for changes on whatever schedule you need. This means that you could theoretically move all of the processing from SharePoint all together.
You could also use a SharePoint Timer job, but this does mean all of the data crunching has to be on an SP Server (either WFE or App server).
If you used an Event Receiver to kick off the job, you then have no control when the processing happens. This means if a lot of users upload a lot of files at a peak time, you could hit performance issues…
So after the file has been uploaded SharePoint in some way has to let SQL Agent know that it’s time to download the file from the library? How can I do so? Event Receiver?
Why don’t you let the sql Server download the excel from the sharepoint library and use a import job.?
You can use something like the import Wizard:Â http://dotnetslackers.com/articles/sql/Importing-MS-Excel-data-to-SQL-Server-2008.aspx
