Batch import of data into SQL server via Sharepoint 2007
Posted on 2010-08-19
I am trying to determine the best technologies/methods for the following.
Here are the scenario points:
We have end users who need to "batch" submit invoicing data into our SQL Server 2008 AP tables.
This user data exists in Excel files. (i.e. we can make them provide it in Excel via a provided template)
These Excel spreadsheets sometimes have tens of thousands of rows, way to much to enter manually.
The user would "submit" this data via a Sharepoint 2007 portal.
When the user submits this batch of data, we need to do several levels of fairly complex validation before it can be accepted into our AP tables:
Is the data the correct data type
Is it within valid ranges etc.
The "valid ranges" validation parameters are stored in our SQL Server 2008 database as they are subject to change. Example: they may be based on weather conditions on the day of service, for that specific location etc., so the validation parameters cannot be included in the Excel template.
If any row of data fail validation, the user then needs to do one of three things, delete that data, change their data or provide an explanation of the difference. So, these validations need to allow an interactive interface.
We have considered a file upload, SSIS import process, but that seems a bit cumbersome. Even though we have a template, it is very easy for the user to modify it, or change it, etc, killing the SSIS process. SSIS does not always provide an error message that the end user could understand.
We have considered Excel services, but Excel data connections do not allow you to update SQL from the Excel sheet.
We are looking at the use of SharePoint lists (can you do copy paste into them from Excel?).
We are open to any of the above, however, would like to hear how you do it. Any suggestions or lessons learned here?
The best answer gets the points! :>)