I started with a package to make sure Transactions are working correctly.
The package level transaction is set to Required. I have two Execute Sql Task, one deletes rows from a table and one does 1/0, to throw the error. Both task are set to supported transaction level and Serializable IsolationLevel. That works.
Now when I replace my two sql task to two separate procedure calls, the first one, ChargeInterest, runs successful but the second one, PaymentProcess, fails always saying.
[Execute SQL Task] Error: Executing the query "Exec
[proc_xx_NotesReceivable_PaymentProcess] ..." failed with the following error:
"Uncommittable transaction is detected at the end of the batch. The transaction
is rolled back.". Possible failure reasons: Problems with the query, "ResultSet"
property not set correctly, parameters not set correctly, or connection not
established correctly.
PaymentProcess being the second stored procedure. Both procedures have there own BEGIN, COMMIT AND ROLLBACKS inside the SP. I believe that the transactions are being successfully handed in the Charge Interest because I can run the following without issues or the dreaded you started with 0 and now have 1 transaction.
EXEC [proc_XX_NotesReceivable_ChargeInterest] 'NR', 'M', 186, 300
EXEC [proc_XX_NotesReceivable_PaymentProcess] 'NR', 186, 300
--OR
GO
BEGIN TRAN
EXEC [proc_XX_NotesReceivable_ChargeInterest] 'NR', 'M', 186, 300
EXEC [proc_XX_NotesReceivable_PaymentProcess] 'NR', 186, 300
ROLLBACK TRAN
Now I have noticed that DTC does get kicked off in both instances? Why I am not sure because it is using the same connection. In the live example I can see the transaction get started but disappears if I put a breakpoint on the PreExecute event of the second stored procedure.
What is the correct way to mingle SP transactions with SSIS transactions?
