Share via

No table or Views could be loaded when creating Excel destination

steve hawk 6 Reputation points
2020-10-02T14:06:57.5+00:00

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

SQL Server Integration Services
SQL Server Integration Services
A Microsoft platform for building enterprise-level data integration and data transformations solutions.
2,525 questions
0 comments

4 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Yitzhak Khabinsky 25,731 Reputation points
    2020-10-02T14:21:07.527+00:00

    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',

    29904-ace-installation-switches-20190823154417.png

    1 person found this answer helpful.
  2. Oleksii Dushyn 6 Reputation points
    2020-10-04T11:05:21.103+00:00

    Hi!
    Check your project properties. First the option "Run64BitRuntime"
    29986-image.png

    1 person found this answer helpful.
    0 comments
  3. steve hawk 6 Reputation points
    2020-10-02T15:15:11.757+00:00

    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

  4. Cris Zhan-MSFT 6,616 Reputation points
    2020-10-05T06:50:57.467+00:00

    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.

    0 comments