How to Use OPENROWSET function within a Transactional SSIS Package
You can turn off DTC Promotion for a linked server

EXEC master.dbo.sp_serveroption @server=N'LincServer', @optname=N'remote proc transaction promotion', @optvalue=N'false'


And allow non-transacted updates for the linked server provider:

EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'NonTransactedUpdates', 1


Which might resolve this issue.  Note for the first one you'd need to create a linked sever definition, instead of using the ad-hoc version of OPENROWSET.

But using ACE from a linked server in an SSIS package is a strange thing to do.  SSIS should connect directly.

From the ACE download page:


  The Access Database Engine 2016 Redistributable is not intended:  . .
  .  To be used by a system service or server-side program where the
  code will run under a system account, or will deal with multiple users
  identities concurrently, or is highly reentrant and expects stateless
  behavior. Examples would include a program that is run from task
  scheduler when no user is logged in, or a program called from
  server-side web application such as ASP.NET, or a distributed
  component running under COM+ services.


Microsoft Access Database Engine 2016 Redistributable 

So it's better to use ACE from a short-lived process, possibly running under a regular user.  For SSIS package execution uses short-lived processes, and you can control the identity of those processes.