A popular issue in importing the data which is in Microsoft Excel 2016 to MSSQL via SSMS (Management Studio). It throw an error :
“===================================
The ‘Microsoft.ACE.OLEDB.16.0’ provider is not registered on the local machine. (System.Data)
To fix this,
First thing first, install “Microsoft Access Database Engine 2016 Redistributable.”
https://www.microsoft.com/en-us/download/details.aspx?id=54920
And then also check the version of Excel and SSMS. They need to be same, like 32 vs 32 or 64 vs 64
SSMS is a 32 bit client, if your Microsoft Excel is 64 bit. you may need to install the 64 bit version of the Microsoft Access Database Engine 2016 Redistributable and try opening – the 64-bit “SQL Server Import and Export Data” directly. Go to Start Menu -> Microsoft SQL Server 2017( your SQL server version) -> SQL Server Import and Export Data (64-bit)> Open the application and try to import data using the “Excel 2016” option, it should work fine.
Another solutions:
Tips 1, use csv instead of xls
What I did is I converted the excel file into a csv file (comma delimited) , then I used the option Data Source “Flat File Source” and it recognized the comma separated fields as columns and created a Table. I also uninstalled the redistributable to keep my machine clean.
Tips 2, use /quite option to install 32bit ACE
I downloaded Microsoft Access Database Engine 2016 Redistributable. I reinstalled 64-bit version. I had no problem since I already had Office 64-bit installed. But, SSMS Import did not work probably for being 32-bit app.
I downloaded 32-bit distributable separately. I tried to run it, but it complained that I already have 64-bit installed. I ran it from the command prompt in /quiet mode. It installed. SSMS import worked on Excel 2016 file now. I did not need even to restart SSMS.