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?

Improve this question
1
  • 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, 2018 at 14:33

2 Answers 2

Reset to default
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.

Improve this answer
0
0

I had this problem where I was trying to write to a spreadsheet through the Microsoft.ACE.OLEDB.12.0 from within a transaction. Because the driver couldn't perform a rollback on the operation being requested, it rejected the connection request. The alternative is to do your main processing within a transaction, then perform the write to the spreadsheet from outside the transaction, but obviously the downside is that whole process is not then atomic.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.