how can i solve "The OLE DB provider "Microsoft.Jet.OLEDB.4.0" has not been registered."?

Question

i try to use get excel data from excel file. i am using office 2007 and sql 2005. i writed below codes:

CREATE TABLE [dbo].[Addresses_Temp] (
    [FirstName]   VARCHAR(20),
    [LastName]    VARCHAR(20),
    [Address]     VARCHAR(50),
    [City]        VARCHAR(30),
    [State]       VARCHAR(2),
    [ZIP]         VARCHAR(10)
)
GO

INSERT INTO [dbo].[Address_Temp] ( [FirstName], [LastName], [Address], [City], [State], [ZIP] )
SELECT [FirstName], [LastName], [Address], [City], [State], [ZIP]
FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
                'Excel 8.0;Database=C:\Source\Addresses.xls;IMEX=1',
                'SELECT * FROM [Sayfa1$]')

Error:Msg 7403, Level 16, State 1, Line 2 The OLE DB provider "Microsoft.Jet.OLEDB.4.0" has not been registered.

How can i solve it?

Answer 1

If you are running the SSIS package from the SQL Agent job then in the step properties after you have picked the ssis package from the MSDB or file system, go to the Execution Options tap and check the Use 32-Bit runtime option.

Worked for me.

Answer 2

For 32-bit SQL Server you will need to install manually as the drivers are not included from MDAC 2.6 onwards. They can be downloaded here

For 64-bit SQL Server I am afraid this is no longer possible. The Jet Engine drivers were not ported to 64-bit and they don't appear to be ported in the future. You can import the Excel document using SSIS but not using the OpenRowSet query. There is another option here but it does seem a some what laborious process