No Tables or Views when Connecting to Excel file in SSIS

Question

Seriously, why in God's name is it SO HARD to connect to Excel!?! Visual Studio 2019 Excel 365 64-bit

I added a Data Flow Task to my Control Flow

Added Excel Source to the Data Flow

Opened the Excel Source and clicked "New" to create a new connection manager. Selected my Excel file (Noted that the Excel version changed from 97-2003 to 2007-2010 after I selected the file). I selected Table or View for Data Access Mode and was greeted with the error:

`No Tables or Views could be loaded`


Could not retrieve the table information for the connection manager 'Excel Connection Manager'.
Failed to connect to the source using the connection manager 'Excel Connection Manager'

I went through and tried every version available and none would connect. A quick search on SO shows that are about 10 BILLION questions on this and the fixes are anywhere from reverting to Windows 98, reinstalling VS, installing 32bit or 64bit Access Database drivers all the way to sacrificing your mouse to the PC Gods.

The purpose of this task is to import the excel file into my SQL database. If I use the SQL Import Wizard it works perfectly, and I actually saved it as a SSIS package and imported it into the package I'm working on now.

I've tried all the versions available in the connection manager, and I installed the 64bit Access Database Engine drivers (it won't allow me to install the 32bit since I have 64bit office installed). I tried changing the Run64BitRuntime option in the project properties.

Am I the only one who thinks it is absolutely ridiculous that this issue has been ongoing for more than 15 years and still isn't resolved?!

Answer 1

Against my better judgement I removed VS2019 and Integration Services. It took me almost 8hrs to get them reinstalled and for everything to work again since MS Visual Studio Tools were not removed or registered correctly.

I have reinstalled everything, rebooted at least a dozen times, and I am still having the same issue where it will not show anything. VERY frustrating since this is the most important part of the SSIS package.

Answer 2

Hi @Sean Hull,

VS2019 is a 32-bit application. It is using Microsoft ACE Provider by loading it into its memory. So, the Microsoft ACE Provider shall be 32-bit too. Both 32-bit and 64-bit Microsoft ACE Provider can coexist on the same machine.

Please follow the following steps:

1. Download and install the 32-bit ACE engine Microsoft.ACE.OLEDB.12.0 at the command prompt with the following flag: AccessDatabaseEngine.exe /quiet
2. Set VS2019 project level setting Run64BitRuntime to False.

All that just for the development machine with the VS2019 on it. No need to do any of it on the SSIS run-time server. You would need there 64-bit ACE engine Microsoft.ACE.OLEDB.12.0

Answer 3

Hi @Sean Hull,

Please try to install the Microsoft.ACE.OLEDB.12.0.

Check Installing the Microsoft.ACE.OLEDB.12.0 Provider for Both 64-bit and 32-bit Processing.

Download: https://www.microsoft.com/en-us/download/details.aspx?id=13255

Regards，

Zoe Hui

---

If the answer is helpful, please click "Accept Answer" and upvote it.