Open Row insert- SQL

Question

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 .

Answer 1

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.