Share via

Issue using Excel source in SSIS 2016

pmscorca 882 Reputation points
2020-12-11T21:23:43.43+00:00

Hi,
in a 64-bit dev machine I've a SQL Server (64-bit) + SSIS 2016 + Visual Studio Community 2019 (with the template for the Integration Services projects) + Access Database Engine 2016 64-bit.
I'm trying to use the Excel source in order to read a *.xlsx file but I cannot see any sheets:

47504-image.png

For the SSIS solution, the Run64BitRuntime debug option is true:

47479-image.png

Now, how could I solve a such issue, please? 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

4 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Yitzhak Khabinsky 25,731 Reputation points
    2020-12-11T21:46:52.68+00:00

    I already answered a similar question here:
    excel-source-stopped-working-in-ssis

    As a pre-requisite, a 32-bit Microsoft.ACE.OLEDB.12.0 (or 15.0 or 16.0) Provider needs to be installed on dev. machine with Visual Studio (VS).

    You need to do 2 things in Visual Studio:

    1. Set the SSIS project in 32-bit mode via Run64BitRuntime to false.
    2. In Excel Source Adapter, specify SQL statement instead of referring to Excel's sheet name only:
      • SELECT * FROM [SheetName$A1:B3], by specifying a certain range
      • SELECT * FROM [SheetName$], whole sheet

    P.S. It seems that it started to happen with the latest ACE 12.0 update v.14.0.7015.1000

    1 person found this answer helpful.
    0 comments
  2. Guoxiong 8,201 Reputation points
    2020-12-12T21:19:12.307+00:00

    Make sure that your computer does not have any previous versions if you already Installed Microsoft Access Database Engine 2016 Redistributable. Otherwise it won't work.

    0 comments
  3. Monalv-MSFT 5,896 Reputation points
    2020-12-14T06:25:48.557+00:00

    Hi @pmscorca ,

    Please download 32bit Access Database Engine and then install 32bit Access Database Engine in CMD using the following command:

    Downloads\AccessDatabaseEngine /quiet
    47863-download32bitaccessdatabase-engine.png
    47757-install32bitaccessdatabase-engine.png

    Best Regards,
    Mona

    ----------

    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.
    Hot issues in November--What can I do if my transaction log is full?
    Hot issues in November--How to convert Profiler trace into a SQL Server table? 5: https://learn.microsoft.com/en-us/answers/articles/67444/email-notifications.html

  4. Sayesd 1 Reputation point
    2022-06-20T05:50:55.443+00:00

    I had the same problem. The solution is simple. Just resave your excel sheet in (97-2003) format and everything will be fine.

    0 comments