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.
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
User contributions licensed under CC BY-SA 3.0