Hope you are Doing well. I have 1 SSIS Package as you can see below :
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