How to Use OPENROWSET function within a Transactional SSIS Package I have an SSIS Package and one of its components is "Execute SQL TasK"
and I've written an OPNEROWSET query inside this task.  When I configure the transaction option of the whole package from "Supported" to "Required" I receive this error:


  The requested operation could not be performed because OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)" does not support the required transaction interface.


When you want to configure the Transaction in SSIS you have to run the service
"Distributed Transaction coordinator" and it seems that this service is in conflict with openrowset. What should I do?  Has anybody come across this issue?
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.