No table or Views could be loaded when creating Excel destination

Question

Hi All,
In my SSIS Package, i created excel destination and new excel connection manager, but when i click on the drop down i am getting the following message
TITLE: Microsoft Visual Studio

---

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'

---

BUTTONS:

OK

---

NO VIEWS OR TABLES CAN BE DOWNLOADED.
I Installed Microsoft Access Database Engine (2010) and still it did not work,
Then another document mentioned that i need to install 32 bit as well, but when i tried to install 32 bit, it wont let me. Please need help, is there a way where i can install both?
Thanks

Answer 1

Hi @steve hawk ,

You have to create the Excel file/sheet before using it in the Excel Connection Manager, and the Excel file should not be open while running the package.

You do need to have installed Microsoft ACE OLEDB Provider, 32-bit edition. It is the only provider Visual Studio can use. Both editions, i.e. 32-bit and 64-bit can coexist on the same machine. You just need to install a 32-bit ACE engine Microsoft.ACE.OLEDB.12.0 in a quiet mode:
e:\Kit\Microsoft Access Database Engine 2010>AccessDatabaseEngine.exe /quiet

You can try to modify the properties of the Excel Destination Component and set Access Mode to "Open RowSet". Additionally, you can specify on the Open RowSet Property the Excel sheet and the Starting and ending cell, i.e. range. For example,'Sheet1$A3:C',

Answer 2

Hi!
Check your project properties. First the option "Run64BitRuntime"

Answer 3

I downloaded the 64 bit from this link
https://www.microsoft.com/en-us/download/Confirmation.aspx?ID=13255
64 bit installed successfully
but when i run the following

C:\>users\shawk\downloads\AccessDatabaseEngine.exe /quiet

nothing happens

Answer 4

Hi @steve hawk ,

Is there any update on this case?

Here are some similar post for reference.
https://stackoverflow.com/questions/23896901/ssis-excel-connection-manager-failed-to-connect-to-the-source
https://stackoverflow.com/questions/43219262/ssis-project-error-could-not-retrieve-the-table-information-for-the-connection

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.