The requested OLE DB provider Microsoft.ACE.OLEDB.12.0 is not registered (SSIS to populate SQL table from Access source)

2

I created an SSIS package last week (my first one) using a guide and it parsed correctly and populated my SQL table. I then consulted another guide to create a job to automate the updating of this table but I discovered this morning that this job has been failing. I exported the log and below is the error. Can someone help me pinpoint what the issue is? I think it may be something along the lines of needing to install Microsoft.ACE.OLEDB.12.0, but I really don't know. I initially checked the box to run in 32bit runtime, but it failed on that as well as when I tried unchecking it, in case that is important.

09/03/2019 10:50:14,Update_Equipment_table,Error,1,SERVER,Update_Equipment_table,Update Equipment Table,,Executed as user: SERVER\SYSTEM. Microsoft (R) SQL Server Execute Package Utility Version 13.0.1601.5 for 64-bit Copyright (C) 2016 Microsoft. All rights reserved. Started: 10:50:14 AM Error: 2019-09-03 10:50:15.94 Code: 0xC0209303
Source: Package Connection manager "\SERVERPATH\ricochet_tanks.mdb" Description: The requested OLE DB provider Microsoft.ACE.OLEDB.12.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: 2019-09-03 10:50:15.94 Code: 0xC001002B Source: Package Connection manager "\SERVERPATH\ricochet_tanks.mdb" Description: The 'Microsoft.ACE.OLEDB.12.0' provider is not registered on the local machine. For more information see http://go.microsoft.com/fwlink/?LinkId=219816 End Error Error: 2019-09-03 10:50:15.94 Code: 0xC020801C Source: Data Flow Task OLE DB Source 1 Description: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager "\SERVERPATH\ricochet_tanks.mdb" 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: 2019-09-03 10:50:15.94 Code: 0xC0047017 Source: Data Flow Task SSIS.Pipeline Description: OLE DB Source failed validation and returned error code 0xC020801C. End Error Error: 2019-09-03 10:50:15.94 Code: 0xC004700C
Source: Data Flow Task SSIS.Pipeline Description: One or more component failed validation. End Error Error: 2019-09-03 10:50:15.94 Code: 0xC0024107 Source: Data Flow Task Description: There were errors during task validation. End Error DTExec: The package execution returned DTSER_FAILURE (1). Started: 10:50:14 AM Finished: 10:50:15 AM Elapsed: 1.422 seconds. The package execution failed. The step failed.,00:00:01,0,0,,,,0

Here is a screenshot of the job details. I believe because I'm using File system as the package source, I have no option to select server credentials or credential type. enter image description here

sql-server
ssis
asked on Stack Overflow Sep 3, 2019 by jw11432 • edited Sep 3, 2019 by jw11432

1 Answer

0

The 64 bit SQL server requires 64 bit ACE driver. If you have the correct driver installed already, need to set the following properties:

EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'AllowInProcess', 1;
GO

EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'DynamicParameters', 1;
GO
answered on Stack Overflow Sep 3, 2019 by PeterHe

User contributions licensed under CC BY-SA 3.0