We help IT Professionals succeed at work.

SQL ROLLBACK in SSIS Job using ODBC

23 Views
Last Modified: 2020-04-27
I am trying to apply TRANSACTION statements in an SSIS package using ODBC. The typical BEGIN TRANSACTION ... ROLLBACK TRANSACTION ... and COMMIT TRANSACTION.

However, I discovered that ODBC doesn't allow TRANSACTION statements.

I found this link that says what to use instead if using ODBC, but it gives no examples at all on how to properly use these commands, and I'm having a hard time finding examples online.

Can someone please point me to a website, or provide in the comments, instructions on how to properly use SQLSetConnectAttr and SQLEndTran to do a rollback when a step in the job fails?
Comment
Watch Question

lcohanDatabase Analyst
CERTIFIED EXPERT

Commented:
In my opinion you are mixing SSIS with "Applications" as the article you are referring to is talking about how to handle transactions from an application  point of view (not SSIS Connection Manager) to the database.

I think you should read the details at links below about how to implement transaction handling in SSIS however please note the last link's restrictions/advice for a successful implementation.

https://www.mssqltips.com/sqlservertip/1585/how-to-use-transactions-in-sql-server-integration-services-ssis/
https://www.mssqltips.com/sqlservertip/3072/sql-server-integration-services-ssis-transactions-without-msdtc/
https://www.tutorialgateway.org/transactions-in-ssis/

https://social.msdn.microsoft.com/Forums/windows/en-US/abeff835-f379-41cb-9e9b-a78a3d27a637/implementing-transaction-in-ssis-package-execute-sql-task-error-failed-to-acquire-connection?forum=sqlintegrationservices

Author

Commented:
Thanks for the reply. So, I already had "RetainSomeConnection" set to True. I also have "TransactionOption" set to Required on the Sequence Container. But I receive this message immediately when I run the job.

There was an error trying enlist an ODBC connection in a transaction. The SQLSetConnectAttr failed to set the SQL_ATTR_ENLIST_IN_DTC attribute.

Not sure what that means, but it happens on the very first step where I'm doing BEGIN TRANSACTION
lcohanDatabase Analyst
CERTIFIED EXPERT

Commented:
Get access with a 7-day free trial.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.