0

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 do realize you can dynamically set your connection managers in SSIS? :) SSIS is about consistency in ETL. OPENROWSET breaks that by performing operations that SSIS was built to do...namely heterogeneous connections across different systems. – clifton_h Dec 15 '18 at 14:33
2

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.

  • Thank you so much for your help . I was wondering if you could explain a little bit more about ACE and the thing you said in your last sentence . Best regards – Shila Dec 16 '18 at 5:14
  • Yes. See the edit. – David Browne - Microsoft Dec 16 '18 at 14:02

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.