Getting errors when running SQL Job that executes .DTSX packages

Question

Hello,

I am running SQL Server 2014. I created three .DTSX packages that all run great when I run them on their own. Each package connects to an Excel file to import data to a SQL table.

I created a SQL Job that has three steps to run each .DTSX package in succession. However, when I run the Job it fails almost instantly. Here is the error that I am getting:

Started: 3:14:25 PM Error: 2020-11-03 15:14:26.01 Code: 0xC0209303 Source: AP_1623 Connection manager "SourceConnectionExcel" Description: 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". End Error Error: 2020-11-03 15:14:26.03 Code: 0xC020801C Source: Data Flow Task 1 Source - content$ [108] Description: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager "SourceConnectionExcel" failed with error code 0xC0209303. There may be error messages posted before this with more information on why the AcquireConnection method call failed. End Error Error: 2020-11-03 15:14:26.04 Code: 0xC0047017 Source: Data Flow Task 1 SSIS.Pipeline Description: Source - content$ failed validation and returned error code 0xC020801C. End Error Error: 2020-11-03 15:14:26.04 Code: 0xC004700C Source: Data Flow Task 1 SSIS.Pipeline Description: One or more component failed validation. End Error Error: 2020-11-03 15:14:26.06 Code: 0xC0024107 Source: Data Flow Task 1 Description: There were errors during task validation. End Error DTExec: The package execution returned DTSER_FAILURE (1). Started: 3:14:25 PM Finished: 3:14:26 PM Elapsed: 0.828 seconds. The package execution failed. The step failed.,00:00:01,0,0,,,,0

Many thanks for any help.

Answer 1

Hi @BS ,

Microsoft.Jet.OLEDB.4.0 provider is very old and deprecated.
It is better to start using Microsoft ACE OLEDB provider while dealing with the MS Excel files.
It has two editions: 32-bit and 64-bit. And 3 versions, you would need just any one of them:

• Microsoft.ACE.OLEDB.12.0
  https://www.microsoft.com/en-us/download/details.aspx?id=13255
• Microsoft.ACE.OLEDB.15.0
  https://www.microsoft.com/en-us/download/details.aspx?id=39358
• Microsoft.ACE.OLEDB.16.0
  https://www.microsoft.com/en-us/download/details.aspx?id=54920

UPDATE
It is very possible that 32-bit ACE engine Microsoft.ACE.OLEDB is already installed on the server. It is not a problem as both 32-bit and 64-bit could coexist on the same server. All what you need to do is to install 64-bit ACE engine Microsoft.ACE.OLEDB in a quiet mode at the command prompt:
e:\Kit\Microsoft Access Database Engine 2010>AccessDatabaseEngine_X64.exe /quiet

Answer 2

Hi @BS ,

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.

1.Please tick 32 bit runtime while configuring the job step.

2.Or we can download and install 64 bit Microsoft ACE OLEDB provider.

Please refer to Microsoft Access Database Engine 2016 Redistributable .

Best Regards,
Mona

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.
Hot issues in October--Users always get connection timeout problem when using multi subnet AG via listener. Especially after failover to another subnet.