Automate Excel upload to hosted SQL Server
Posted on 2014-10-23
We have a database running on a hosted server which consist of 3 tables -table1, table2, and table3. Data is locally collected using using Excel. So each worksheet corresponds to each table in the SQL database and the columns to the fields in the database tables (table1, table2, and table3).
Currently I am manually activating task, import data, choose a datasource etc... to import the Excel data onto the hosted server.
Since I am running SQL Server Express to access the hosted database, I do not have the SSIS option. In addition, the hosted server does not allow openrowset.
What is the best way to automate the import process from the Excel worksheets to the tables on the hosted database ?