[sql-server] 'Microsoft.ACE.OLEDB.16.0' provider is not registered on the local machine. (System.Data)

I get this error when I try to connect to any excel through MSSQL Server Data Import i.e. SSIS package The Microsoft.ACE.OLEDB.16.0 provider is not registered on the local machine. (System.Data)

It is not the same version I think some other patching is needed

This question is related to sql-server excel ssis oledb aceoledb

The answer is


Note: I am running SQL 2016 Developer 64bit, Office 2016 64bit.

I had the same issue and solved it by downloading the following:

  1. Download and install this: https://www.microsoft.com/en-us/download/details.aspx?id=54920

  2. Whatever file you are trying to access/import, make sure you select it as a Office 2010 file (even though it might be a Office 2016 file).

It works.

Source


For anyone that is still stuck on this issue after trying the above. If you are right-clicking on the database and going to tasks->import, then here is the issue. Go to your start menu and under sql server, find the x64 bit import export wizard and try that. Worked like a charm for me, but it took me FAR too long to find it Microsoft!


As a quick workaround I just saved the workbook as an Excel 97-2003 .xls file. I was able to import with that format with no error.


An alternative that works for me is to simply convert to a CSV.


If you have OS(64bit) and SSMS(64bit) and already install the AccessDatabaseEngine(64bit) and you still received an error, try this following solutions:

1: direct opening the sql server import and export wizard.

if you able to connect using direct sql server import and export wizard, then importing from SSMS is the issue, it's like activating 32bit if you import data from SSMS.

Instead of installing AccessDatabaseEngine(64bit) , try to use the AccessDatabaseEngine(32bit) , upon installation, windows will stop you for continuing the installation if you already have another app installed , if so , then use the following steps. This is from the MICROSOFT. The Quiet Installation.

If Office 365 is already installed, side by side detection will prevent the installation from proceeding. Instead perform a /quiet install of these components from command line. To do so, download the desired AccessDatabaseEngine.exe or AccessDatabaeEngine_x64.exe to your PC, open an administrative command prompt, and provide the installation path and switch Ex: C:\Files\AccessDatabaseEngine.exe /quiet

or check in the Addition Information content from the link below,

https://www.microsoft.com/en-us/download/details.aspx?id=54920


ACE.oledb.16.0 dosen't work in the 64-bit os

download patch from https://www.microsoft.com/en-us/download/details.aspx?id=13255


You have to use now the new XLSX-Driver from Access-Redist (32/64-Bit). The current XLS-Driver are corrupted since last cumulative update.


After having both installed the Microsoft Access Database Engine 2016 Redistributable and found the x64 bit import export wizard as instructed in the answers above, I had one last hoop to jump through. I got this error 'Executing the query "CREATE TABLE ...." failed with the following error: "Culture is not supported ... 3072 (0x0c00) is an invalid culture identifier."'

The fix was to go to Control Panel -> Clock and Region -> change my "English (Finland)" region to pure Finnish (Finland). Other more traditional cultures such as en-US will work too. I think I first saw this solution here: Fix culture is not supported: 3072 by changing Region Formats


Follow these steps:

  1. Go [here][1], download Microsoft Access Database Engine 2016 Redistributable and install
  2. Close SQL Server Management Studio
  3. Go to Start Menu -> Microsoft SQL Server 2017 -> SQL Server 2017 Import and Export Data (64-bit)
  4. Open the application and try to import data using the "Excel 2016" option, it should work fine.

Questions with sql-server tag:

Passing multiple values for same variable in stored procedure SQL permissions for roles Count the Number of Tables in a SQL Server Database Visual Studio 2017 does not have Business Intelligence Integration Services/Projects ALTER TABLE DROP COLUMN failed because one or more objects access this column Create Local SQL Server database How to create temp table using Create statement in SQL Server? SQL Query Where Date = Today Minus 7 Days How do I pass a list as a parameter in a stored procedure? SQL Server date format yyyymmdd SQL Server IF EXISTS THEN 1 ELSE 2 'Microsoft.ACE.OLEDB.16.0' provider is not registered on the local machine. (System.Data) How to add a boolean datatype column to an existing table in sql? How to import an Excel file into SQL Server? How to use the COLLATE in a JOIN in SQL Server? Change Date Format(DD/MM/YYYY) in SQL SELECT Statement Stored procedure with default parameters Drop view if exists Violation of PRIMARY KEY constraint. Cannot insert duplicate key in object How to update large table with millions of rows in SQL Server? Could not find server 'server name' in sys.servers. SQL Server 2014 How to create a Date in SQL Server given the Day, Month and Year as Integers Select Rows with id having even number A connection was successfully established with the server, but then an error occurred during the login process. (Error Number: 233) SQL Server: Error converting data type nvarchar to numeric How to add LocalDB to Visual Studio 2015 Community's SQL Server Object Explorer? Using DISTINCT along with GROUP BY in SQL Server Rebuild all indexes in a Database How to generate Entity Relationship (ER) Diagram of a database using Microsoft SQL Server Management Studio? The target principal name is incorrect. Cannot generate SSPI context How Stuff and 'For Xml Path' work in SQL Server? How to view the roles and permissions granted to any database user in Azure SQL server instance? How do I create a local database inside of Microsoft SQL Server 2014? Format number as percent in MS SQL Server MSSQL Regular expression How to select all the columns of a table except one column? SQL count rows in a table EXEC sp_executesql with multiple parameters SQL Server : How to test if a string has only digit characters Conversion of a varchar data type to a datetime data type resulted in an out-of-range value in SQL query Remove decimal values using SQL query How to drop all tables from a database with one SQL query? How to get last 7 days data from current datetime to last 7 days in sql server Get last 30 day records from today date in SQL Server Using Excel VBA to run SQL query No process is on the other end of the pipe (SQL Server 2012) How to subtract 30 days from the current date using SQL Server Calculate time difference in minutes in SQL Server How to join two tables by multiple columns in SQL? The database cannot be opened because it is version 782. This server supports version 706 and earlier. A downgrade path is not supported

Questions with excel tag:

Python: Pandas pd.read_excel giving ImportError: Install xlrd >= 0.9.0 for Excel support Converting unix time into date-time via excel How to increment a letter N times per iteration and store in an array? 'Microsoft.ACE.OLEDB.16.0' provider is not registered on the local machine. (System.Data) How to import an Excel file into SQL Server? Copy filtered data to another sheet using VBA Better way to find last used row Could pandas use column as index? Check if a value is in an array or not with Excel VBA How to sort dates from Oldest to Newest in Excel? Creating an Array from a Range in VBA Excel: macro to export worksheet as CSV file without leaving my current Excel sheet VBA: Convert Text to Number EPPlus - Read Excel Table How to label scatterplot points by name? What's the difference between "end" and "exit sub" in VBA? Rename Excel Sheet with VBA Macro Extract Data from PDF and Add to Worksheet Quicker way to get all unique values of a column in VBA? Multiple conditions in an IF statement in Excel VBA How to find and replace with regex in excel Unprotect workbook without password Excel is not updating cells, options > formula > workbook calculation set to automatic Find row number of matching value If "0" then leave the cell blank Clear contents and formatting of an Excel cell with a single command Remove Duplicates from range of cells in excel vba Delete worksheet in Excel using VBA Get list of Excel files in a folder using VBA Excel doesn't update value unless I hit Enter Declare a variable as Decimal Parse XLSX with Node and create json Detect if a Form Control option button is selected in VBA Get length of array? Object of class stdClass could not be converted to string - laravel Java - Writing strings to a CSV file Quickest way to clear all sheet contents VBA VBA: Counting rows in a table (list object) Excel VBA If cell.Value =... then VBA Excel - Insert row below with same format including borders and frames excel - if cell is not blank, then do IF statement filter out multiple criteria using excel vba Referencing value in a closed Excel workbook using INDIRECT? Use Excel VBA to click on a button in Internet Explorer, when the button has no "name" associated IndexError: too many indices for array File name without extension name VBA (Excel) Conditional Formatting based on Adjacent Cell Value Easy way to export multiple data.frame to multiple Excel worksheets Using ExcelDataReader to read Excel data starting from a particular cell What are the RGB codes for the Conditional Formatting 'Styles' in Excel?

Questions with ssis tag:

Visual Studio 2017 does not have Business Intelligence Integration Services/Projects 'Microsoft.ACE.OLEDB.16.0' provider is not registered on the local machine. (System.Data) Text was truncated or one or more characters had no match in the target code page including the primary key in an unpivot Microsoft.ACE.OLEDB.12.0 is not registered SSIS Excel Connection Manager failed to Connect to the Source The value violated the integrity constraints for the column Error: 0xC0202009 at Data Flow Task, OLE DB Destination [43]: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E21 how to resolve DTS_E_OLEDBERROR. in ssis SSIS - Text was truncated or one or more characters had no match in the target code page - Special Characters SSIS expression: convert date to string SSIS Convert Between Unicode and Non-Unicode Error Using SSIS BIDS with Visual Studio 2012 / 2013 SSIS cannot convert because a potential loss of data How to access ssis package variables inside script component How do I convert number to string and pass it as argument to Execute Process Task? SSIS Connection not found in package Temporarily disable all foreign key constraints Update Rows in SSIS OLEDB Destination Oracle client and networking components were not found How to pass variable as a parameter in Execute SQL Task SSIS? How to loop through Excel files and load them into a database using SSIS package? How do I fix 'Invalid character value for cast specification' on a date column in flat file? SSIS package creating Hresult: 0x80004005 Description: "Login timeout expired" error How do I format date value as yyyy-mm-dd using SSIS expression builder? How to create a temporary table in SSIS control flow task and then use it in data flow task? Convert from DateTime to INT SSIS Text was truncated with status value 4 How to increase MaximumErrorCount in SQL Server 2008 Jobs or Packages? SSIS how to set connection string dynamically from a config file How do I edit SSIS package files? Import Package Error - Cannot Convert between Unicode and Non Unicode String Data Type SSIS Connection Manager Not Storing SQL Password How do I view the SSIS packages in SQL Server Management Studio? AcquireConnection method call to the connection manager <Excel Connection Manager> failed with error code 0xC0202009 Watching variables in SSIS during debug SSIS Excel Import Forcing Incorrect Column Type What is the SSIS package and what does it do? How to execute an SSIS package from .NET?

Questions with oledb tag:

'Microsoft.ACE.OLEDB.16.0' provider is not registered on the local machine. (System.Data) How to import an Excel file into SQL Server? how to resolve DTS_E_OLEDBERROR. in ssis Check if ADODB connection is open Import Excel to Datagridview "Cannot create an instance of OLE DB provider" error as Windows Authentication user Optimal way to Read an Excel file (.xls/.xlsx) How to connect to a MS Access file (mdb) using C#? "The 'Microsoft.ACE.OLEDB.12.0' provider is not registered on the local machine" Error in importing process of xlsx to a sql server How can I export data to an Excel file DataAdapter.Fill(Dataset) Microsoft.Jet.OLEDB.4.0' provider is not registered on the local machine Using Excel OleDb to get sheet names IN SHEET ORDER AcquireConnection method call to the connection manager <Excel Connection Manager> failed with error code 0xC0202009 Microsoft.ACE.OLEDB.12.0 provider is not registered what is the difference between OLE DB and ODBC data sources?

Questions with aceoledb tag:

'Microsoft.ACE.OLEDB.16.0' provider is not registered on the local machine. (System.Data) 'Microsoft.ACE.OLEDB.12.0' provider is not registered on the local machine