Thank you all for the suggestions. We ended up raising a support ticket to Microsoft and it was concluded that the user that executes/calls the package must have "Interactive Login"...

Initially we had x64 version of Access. But we tried installing x86 version along with it but did not work that way as well.

Also, as the answer in above Stackoverflow...

Little correction on my previous comment: It seems like we need remote admin privilege on the server where SSIS catalog is running and not the 'sysadmin' privilege on SQL Server...

Yes, 'admin privilege' referring to another user with sysadmin -  (a DBA in our organization) executing the package. Also, he tried it with SQL Agent job with SQL Server agent...

We did try changing the temp folder and executing the package. Still no luck.