SSIS Custom Control UOW (Unit of work) to maintain the transaction

Question

Hello,

Currently we are working on SSIS and working on specific case where we need to implement the unit of work design for our custom created controls. The purpose of UOW (unit of work) is to maintain the transaction at root level and controlling the sub transaction done through custom controls. Let me brief you more:

We have created our custom SSIS controls to match our requirement. Out of which, one of the custom control is for Web API. The purpose of this control is to call the existing Web API and get the result in JSON. We want to call multiple Web APIs through SSIS package and to have shared transaction across all the sequence of API call. Note that, each Web API have its own transaction handled at API level. Just to note, SSIS and API both will be hosted on different servers.

Consider below example to understand the flow in SSIS:

1. Call API-1 (That will do relative operations. Contains its own transaction)
   ↓
2. Call API-2 (That will do relative operations. Contains its own transaction)
   ↓
3. Call API-3 (That will do relative operations. Contains its own transaction)
   ↓
4. Call API-4 (That will do relative operations. Contains its own transaction)

As said, each API call will have its own transaction implemented. Meaning, here four dedicated tasks will have four transactions.

What we want is, if any of the Web API call fails, we want to roll back the previous API operations. Meaning, if API-1 and API-2 are successfully executed but API-3 fails then roll back the API-1 and API-2 operations. We tried maintaining the transactions at sequence container level (keeping MSDTC enabled). Meaning,

1. Sequence Container (Transaction = Required, IsolationLevel = Serializable)
   a. API-1 call
   b. API-2 call
   c. API-3 call
   d. API-4 call

Since we are using our custom control, it is not able to roll back. If we use SQL Tasks instead of custom Web API control, it is doing its job perfectly.

Could anyone suggest any idea/direction/thought how to maintain distributed/shared transaction over custom controls in SSIS?

Answer 1

Hi @Sohil ,

Please use Event Handlers in SSIS package.

The following links will be helpful:

Integration Services (SSIS) Event Handlers

Event Handlers in SSIS

Best Regards,
Mona

----------

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.
Hot issues in October--Users always get connection timeout problem when using multi subnet AG via listener. Especially after failover to another subnet.