New to the SSIS world and I got it working before on Excel Version 2016 & Version 2007-2010 [after ages of troubleshooting] but then it just stopped working [endless testing on various solutions I've read, seems to be a reoccurring issue] and now it continues to not work.... any assistance would be appreciated.
Here's the situation: Importing Excel to the SSIS environment via VS19. when I try to view the Table in the Excel Source Editor, it comes up with the following error message:
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 am running the package in 32-bit mode
Project properties >> Debugging >> Run64BitRuntime = False
I have installed both AcccessDatabaseEngine 32bit and 64bit for 2010 & 2016 on my computer -- x64 2010 redistributable - 14.0.7015.1000 -- x32 2010 redistributable - 14.0.7015.1000 -- x64 2016 redistributable - 16.0.4519.1000 -- x32 2016 redistributable - 16.0.4519.1000
Have even done the Passive/Quiet model installation steps
cmd
in the Windows search box under the Start menu and selecting cmd.exe
/passive
or /quiet
[if passive didn't work].regedit
in the Windows search box under the Start menu and selecting regedit.exe
"HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Office\14.0\Common\FilesPaths"
[above for 2010] and [below for 2016]
"HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Office\16.0\Common\FilesPaths"
I've even tried the DelayValidation method someone suggested [still didn't work]
Project Task >> Properties >> DelayValidation = True
Even checked my connection string on my Excel Connection Manager
Excel Connection Manager Connection >> Properties >> ConnectionString >> Provider=Microsoft.ACE.OLEDB.12.0
Ran a repair on both my VS19 and my Office
Current workaround: I've had to save all my excel files to a .xls versions and change the config to 97-2003 in order for my SSIS to register the tables from the workbook. What am I doing wrong? Am I missing something? It was working before and now it's C#$% itself.
Can someone please suggest another solution of why I can't use my excel 2010, 2013 or 2016 version to import my Excel Source to my SSIS environment in VS19 please. Thank you.
So, I basically had to start from scratch. Wipe my laptop and reinstall everything. SSMS, VS all the add-ons, everything. Now it works perfectly, so I think it's either the sequence that I've downloaded things or something that was already in my laptop was stopping the applications from working properly. Gosh that was a nightmare. But my problem is now fixed.