Importing Excel File into SQL Server 2022 using SSIS

Question

I'm migrating my SSIS jobs into a new server. The new server is using Windows 2022 server as its OS, with SQL Server 2022 and has Visual Studio 2022.

One of my jobs uses the Excel Source along with an Excel Connection Manager. The Excel file is downloaded from the web and is a .xls file. I have no control over this as it is not a company file.

When I click on the Excel source in Visual Studio, I get the following error:

The requested OLE DB provider Microsofot.Jet.OLEDB.4.0 is not registered.

To remedy this problem I have done the following:

1. Downloaded and Installed the 64 bit Microsoft OLE Db Driver for SQL Server
2. Downloaded the Microsoft Visual C++ Redistributable (per step 1 instructions)
3. Downloaded and installed the 64bit version of the Access Database Engine Redistributable (2016)
4. Tried to run in 32 bit; however, it turns out that VS 2022 will only run in 64 bit
5. Repaired my installation of VS 2022 just in case it was corrupt.

These steps have not solved the problem as I still get that error. I'm not married to using this method of getting an Excel sheet into SQL Server; however, I do need to automate this process and it has to read a .xls file.

Please remember this SSIS job has been working for several years on my old server so I am ruling out incorrect flow/design. And I have updated the file location in the Excel connection dialog box.

Thoughts? Answers?

Answer 1

In the SSIS package data flow, edit the Excel Connection Manager and set Excel version to "Microsoft Excel 2007 - 2010". This will change the provider to Microsoft.ACE.OLEDB.12.0 which is the driver installed by the Microsoft 365 Access Runtime 64bit which you have already installed (point 3 in your post).