1

When we run a SSIS package to import data from excel , we get an initial error:

The requested OLE DB provider Microsoft.Jet.OLEDB.4.0 is not registered.
If the 64-bit driver is not installed, run the package in 32-bit mode.

We were able to fix this by executing the package in 32 bit mode using the option in wizard

SELECT * 
INTO #temp13
FROM OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0',   
                    'Data Source=XXXXXXXX;Extended Properties=Excel 8.0')...[Sheet1$];

We get the below error

The 32-bit OLE DB provider "Microsoft.Jet.OLEDB.4.0" 
cannot be loaded in-process on a 64-bit SQL Server. [SQLSTATE 42000] (Error 7438).
The step failed.

Is there any option to run the Transaction SQL in 32 bit .

1

1 Answer 1

0

This might be helpful:

  1. Download the Microsoft.ACE.OLEDB.12.0 for Windows, 64 bit version found here: https://www.microsoft.com/en-us/download/details.aspx?id=13255
  2. Install it on your server.
  3. Check the user running SQL Server and make sure that user has access to the temp directory C:\Windows\ServiceProfiles\LocalService\AppData\Local\Temp if it's a local service account or C:\Windows\ServiceProfiles\NetworkService\AppData\Local\Temp if it's a network service account.
  4. Configure 'Ad Hoc Distributed Queries' and enable the Microsoft.ACE.OLEDB files like this:

Here's the SP_CONFIGURE/RECONFIGURE commands:

EXEC SP_CONFIGURE 'show advanced options', 1; 
GO 
RECONFIGURE; 

EXEC SP_CONFIGURE 'Ad Hoc Distributed Queries', 1; 
GO 
RECONFIGURE; 

EXEC sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'AllowInProcess', 1   
EXEC sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'DynamicParam', 1

Based on your SQL Server version, you might need to use DynamicParameters instead of DynamicParam

FYI, On SQL Server 2014, we have used EXEC sp_configure and use DynamicParameters instead of DynamicParam

EXEC sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'DynamicParameters', 1

Make sure you register msexcl40.dll:

regsvr32 C:\Windows\SysWOW64\msexcl40.dll

Edited:

You might have to check the excel versions like xlsx or xls. Please check below screenshot.

enter image description here

2
  • Thanks , After installing the driver , XLSX files worked but not XLS . We are using data from mutliple source so few files are in XLS .
    – Lakshmi R
    Feb 21, 2023 at 5:42
  • @LakshmiR did you choose the excel version for XLSX or XLS?
    – CR241
    Feb 22, 2023 at 17:42

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.