How to periodically update SQL Server table from an Excel sheet

Question

For my .NET Application I have an Excel file that is used as the data source. This Excel file is updated every hour. My current back-end database technology is MS Access. The Access file has a linked table to the mentioned Excel file. I would like to switch over my back-end to SQL Server.

My question is how can I create a linked table in SQL Server such that I always have the current data in my SQL Server database. Right now I have managed to import the Excel sheet through the import wizard in SQL Server Mgmt. Studio. But obviously this is static (not updated hourly like the excel sheet).

Can I create some sort of procedure that runs every hour, reads the Excel sheet and import to my SQL Server database? How would I go about doing that? Or any other method as long as my table in SQL Server gets updated on an hourly basis with data from the Excel sheet.

Answer 1

One, do not code read "in process" from the excel file. That is a disaster waiting to happen. Some user opens the file, locks it, and now your sql-server starts failing. :) That is a bad scenario.

You should create an ETL package to run (hourly)?

https://learn.microsoft.com/en-us/sql/integration-services/ssis-how-to-create-an-etl-package?view=sql-server-ver15

What is SQL Server Integration Services (SSIS)? MicrosoftSQL Server

Integration Services (SSIS) is a platform for building high-performance data integration solutions, including extraction, transformation, and load (ETL) packages for data warehousing. SSIS includes graphical tools and wizards for building and debugging packages; tasks for performing workflow functions such as FTP operations, executing SQL statements, and sending e-mail messages; data sources and destinations for extracting and loading data; transformations for cleaning, aggregating, merging, and copying data; a management database, SSISDB, for administering package execution and storage; and application programming interfaces (APIs) for programming the Integration Services object model.

data sources and destinations for extracting and loading data;

Deploy and Schedule:

https://www.mssqltips.com/sqlservertutorial/9069/deploy-and-schedule-an-sql-server-integration-services-ssis-package-step-by-step/

Scheduling the SSIS Package with SQL Server Agent Manually executing packages is one thing, but normally you will schedule packages so your ETL can run in a specific time windows (probably at night). The easiest option is SQL Server Agent. You can right-click on the Jobs node to create a new job: