Issue using Excel source in SSIS 2016

Question

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:

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

Now, how could I solve a such issue, please? Thanks

Answer 1

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

Answer 2

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.

Answer 3

Hi @pmscorca ,

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

Downloads\AccessDatabaseEngine /quiet

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

Answer 4

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