2

My SSIS job hosted in DEV server got failed due to the the following SSIS Error

Error Code DTS_E_OLEDBERROR.  An OLE DB error has occurred. Error code: 0x80004005.
An OLE DB record is available.  Source: "Microsoft JET Database Engine"  Hresult: 0x80004005  Description: "Unexpected error from external database driver (1).".

In my SSIS package -> Data Flow Task, I am using source as SQL server and destination as Excel. Excel Version is 97-2003 (xls format) and the connection details is:

Provider=Microsoft.Jet.OLEDB.4.0;Data Source={mypath}\filename.xls;Extended Properties="EXCEL 8.0;HDR=YES";

It was running successfully every one hour as scheduled.

The issue occurred when DB instances stopped for windows patching and after started on patching completion

I am facing this issue in the server. I have checked other solutions in Stack Overflow. It was mainly discussed with size based. In my case, SSIS job never changed. I believe this is due to Jet engine driver level issue. I am more looking for driver level check rather than ssis job level code change, as the code never changed and no large data flow is happened.

halfer
  • 19,824
  • 17
  • 99
  • 186
Viswa
  • 745
  • 10
  • 30
  • I'm seeing the same issue on SQL Server 2016. Looks like it started happening for us after the server rebooted on 11th October when KB2553338 was installed. Our Access Database Engine 2010 also appears to have been updated/installed by Windows Update on that date (probably this KB that updated it). – Matty Brown Oct 13 '17 at 12:37

6 Answers6

4

Same for me after switching to the second node of the cluster after applying wsus updates. Seems to be related to the last updates.

I've filed a connect bug: https://connect.microsoft.com/SQLServer/feedback/details/3142556

Just uninstalled: KB 4040685 (This is on my workstation, too) KB 4041693 KB 4041687

It's working fine now!

Christoph Muthmann
  • 56
  • 3
  • I've voted for your feedback report on Microsoft Connect. Having the same issue here. Maybe it's time I updated our old .xls reports to .xlsx... still annoying that Microsoft have broken this functionality with another botched Windows Update. – Matty Brown Oct 13 '17 at 12:44
  • HI Christoph, Yes. I have uninstalled the update and the issue got fixed now. Its all about the security update patching from microsoft. Thanks for your answer. – Viswa Oct 20 '17 at 10:43
2

Same here as well. After applying updates to the server, packages which use Excel 97 - 2003 format have failed.

We are having to switch each connection to 2007 to enable the package to run on the server.

Malice345
  • 21
  • 1
  • Hi @Malice345, did changing each connection to Excel 2007+ work for you? We're using OLE DB Source and Destination in our project. Which tools did you use? We're coming from SQL Server 2016 to Excel 2010. – Matty Brown Oct 13 '17 at 12:47
  • Used an Excel Destination - had to re-map the columns, but apart from that no problems. Not going to remove the Windows Updates, since the issue no longer affects us. – Matty Brown Oct 13 '17 at 13:13
1

The best recommended solution is to move to Microsoft ACE OLE DB provider.

Microsoft is working on a resolution and will provide an update in an upcoming release of the security patch. This is expected to be available in another 2-3 weeks or earlier.

More info...

bgarcia
  • 51
  • 1
  • 2
0

I installed Access Database Engine Tools 2010, after this I changed the Excelversion from 2003 tol 2007 in connection. Then I again can execute my extract packages form XLS files, without having to uninstall KB's BR Annette

Annette
  • 1
0

Change every datasource from "Provider=Microsoft.Jet.OLEDB.4.0" to "Provider=Microsoft.Jet.OLEDB.12.0"

This error ocurs when installed updates: KB4040685, KB4041693, KB4041687

Isaac Sánchez
  • 36
  • 1
0

In my case – we have bunch of packages uses older version of excel. Upgrading/Modifying packages is not solution without going thru SDLC process.

As Temp Work Around – We un-installed the security patches - KB4041679 & KB4041690 and things started working.

First, We did uninstalled KB 4040685 and issue still persists.

We are using Microsoft Jet 4.0 OLE DB Provider to read data from Excel(.XLS) file.

Thanks, Sri

Srihari Kagita
  • 1
  • 1