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 .