We are trying to load XLSX files by running SSIS packages via SQL Server Jobs that are run by Proxy accounts.
The SSIS packages run without issues when manually processed.
The SQL jobs run without issues when the Proxy account has System Admin access.
The SQL jobs fail to run when the Proxy accounts are set to our SSIS Proxy account level access.
These are the errors that we are seeing:
"SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005. An OLE DB record is available. Source: "Microsoft Access Database Engine" Hresult: 0x80004005 Description: "Unexpected error from external database driver(1)."
Provider=Microsoft.ACE.OLEDB.12.0;Data Source=\serverName\Drive\fileName.xlsx;Extended Properties="EXCEL 12.0 XML;HDR=YES";
This is what we have done to try and resolve this issue:
Set Proxy account to read/write on temp folders
Confirmed Proxy account can read/write to local folder path
Confirmed Proxy account can access file (copy to new folder, delete from current folder, move from remote folder to local folder)
Confirmed Admin account can load file via SQL job
Uninstalled and reinstalled Access drivers (then repeated above tests)
Tested same file name, data, tables(etc) with xls file
-xls file runs without issue, but files are going to be arriving as xlsx