Post reply

SQL / SSIS 2019 and Excel Connection manager

  • December 16, 2021 at 3:50 pm

    #3964436

    We're migrating to 2019 and we've had the predictable meltdown of an SSIS 2012 package with an Excel connection manager. I've installed the 2016 Microsoft Access Database Engine redistributables (32 bit and 64 bit) on the server, but that hasn't helped the issue. We're still getting the "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. Error code: 0x00000000.

    An OLE DB record is available. Source: "Microsoft OLE DB Service Components" Hresult: 0x80040154 Description: "Class not registered"." error.

    I am running the package in 32 bit mode. I've tried recreating the connection manager, but no matter what I do, I still get this "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=\\Nas\Share\Directory\MyFile.xls;Extended Properties="EXCEL 8.0;HDR=YES";".

    Trying to change it to "Provider=Microsoft.ACE.OLEDB.16.0;Data Source=\\Nas\Share\Directory\MyFile.xls;Extended Properties="EXCEL 8.0;HDR=YES";" or "Provider=Microsoft.ACE.OLEDB.16.0;Data Source=\\Nas\Share\Directory\MyFile.xls;Extended Properties="EXCEL 16.0;HDR=YES";" makes the package freak out.

    Any thoughts as to what I could be missing here?

    Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

  • December 16, 2021 at 5:18 pm

    #3964470

    I've done some playing around with this and encountered exactly the same problem as you with the standard Excel source – a Jet error.

    The way I got it to work was by using an OLEDB connection. Change the OLE DB Provider to match the ACE driver version you have installed, enter the path to the file in the 'Server or file name' box and then click on the ALL icon to display all properties.

    In the 'Advanced' section, enter the following against 'Extended Properties'

    Excel 8.0;HDR=YES

    After doing this, you should be able to do a successful 'Test Connection'.

  • December 21, 2021 at 10:04 am

    #3965859

    Were you ever able to fix this issue? How did you do it?

Viewing 3 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Cookies on SQLServerCentral

This website stores cookies on your computer.

These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.

To find out more about the cookies we use, see our Privacy Policy