0

Hope you are Doing well. I have 1 SSIS Package as you can see below :

enter image description here

In the "Insert" part of the Package which is an Execute sql task, I'm executing a Stored procedure . this is the code inside the task

(Exec [dbo].[Main] ?,?,?,?,?)

Which has a transnational format like this Begin Try Begin Tran..... Commit Tran End Try / Begin Catch Roll Back Tran .. End Catch . And Then I configured the whole package as one "Transaction" by setting the transaction option of my entire package to "Required" . After executing the package I run into this error (The error is for Execute sql task) : "Cannot roll back Main. No transaction or savepoint of that name was found."

Guys , I'm sure that this error is because I'm calling a stored procedure which has a transnational format inside another transaction which is the entire package but are there any ways that I can handle this? The stored procedure must have the transnational format because I'm handling error inside it.

Thanks in advance

Update : After removing the name "Main" From my transaction I ran into this error

:Distributed transaction completed. Either enlist this session in a new transaction or the NULL transaction

  • I don't think there's a good solution. The best practice is to perform transaction management in the client app (SSIS here), server-side T-SQL, but never both. – Dan Guzman Nov 18 '18 at 11:30
  • So you recommend I have to manage transaction in one side? and it's better to handle it in SSIS ? – Yavar Nov 18 '18 at 11:35
  • I suggest you to write it as the answer of the question so that I can choose it as the answer – Yavar Nov 18 '18 at 11:58
1

The general best practice is to perform transaction management in either the client application (SSIS), server-side T-SQL, but not both. This allows the SSIS package to determine and control the transactional unit of work and manage transactions among tasks that support transactions. Individual stored procedures shouldn't start transactions in this case since the outer transaction will ensure all-or-none behavior.

You could instead design the package such that it is idempotent (rerunnable) and not use transactions at all, perhaps also using checkpoints to avoid rework of expensive processing. In that case, the proc could be coded to insert only if the new row doesn't already exist. This technique helps avoid issues caused by long-running transactions, such as blocking and large transaction logs.

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.