Transaction support when using OleDb connection manager in Custom SSIS Data Flow component

Question

I've googled a lot and repeatedly stumbled upon a few articles which make me feel - this ain't gonna work!

As per this link https://learn.microsoft.com/en-us/sql/integration-services/extending-packages-scripting/data-flow-script-component/connecting-to-data-sources-in-the-script-component?view=sql-server-2014

When you write managed code in a Script component, you cannot call the AcquireConnection method of connection managers that return unmanaged objects, such as the OLE DB connection manager and the Excel connection manager. However, you can read the ConnectionString property of these connection managers, and connect to the data source directly in your code by using the connection string of an OLEDB connection from the System.Data.OleDb namespace.

And I believe, if I cannot call Acquire Connection, then I cannot participate in the transaction which the component container has started in the SSIS package.

My existing SSIS packages use OLEDB Connection Managers (Native) and the same connection manager will be used by my custom component. At the moment, I'm using the connection string without calling Acquire Connection - in this approach since I'm creating a new connection, the component cannot participate in any transaction which the parent SSIS sequence container might have started, which in turn does not cause rollback of changes done by the custom component in case failures happen downstream. And changing the existing connection managers to ADO.NET doesnt look feasible due to huge impact since the existing OleDb connection managers are used in many OleDb components like OleDb destination in existing code.

Is there any way to work around this issue - so basically I want to get transactions to work using OleDb connection manager in a custom component?

Answer 1

Well, DbConnection and its derivative, SqlConnection class has method EnlistTransaction, which allows to add (enlist) this connection to a distributed transaction. Please note that is is a Distributed Transaction managed by MS DTC, not native MS SQL transaction.
Theoretically (I have never done that in such specific scenario), you can open a managed connection based on the connection string you derived from OLE DB conn manager, and enlist it into a transaction. Things to look for and to check in the development:

• Install, configure and ensure MS DTC works and is accessible both by server where package is running and DB Server.
• If there is no Distributed Transaction in package (when TransactionSupport <> Required), transaction parameter will be null. You should handle this case in code as well.
• Transaction parameter in AquireConnection has type object, the docs say that it is a handle to a distributed transaction. I would do a breakpoint right on entering AquireConnection method and check its real structure; you need to cast it to System.Transactions.Transaction type.
• You can try to get an ambient distributed transaction by calling System.Transactions.Transaction.Current. Perhaps, it might work.

Please update on your results.