We currently have several spreadsheets of data that are being updated by individual users, the problem is when we would like to provide reporting across all of these spreadsheets in one report. The data is updated biweekly with new data.
We are currently maintaining an Excel Workbook which links to all of the workbooks and brings all of the data into one view. This one excel workbook is linking 17 different excel workbooks. We also have an issue when we need to add another workbook to this environment - it takes a lot of time.
I have been researching using SSIS Package to update the data in the tables, but in its basic form, it looks like I would need to delete the table and create a new table each time otherwise I am simply continuing to add additional rows tot he table each time.
We are considering that it may be easier to:
1. Import the Excel data into Sql tables and report out of Sql.
2. Link the excel spreadsheets to sql so that the data can me maintained and updated and then the reports would be out of SQL.
Please provide your thoughts and possible solutions to the above question.