TransactionOption in SSIS

问题:

I have created a SSIS package. I need to apply Transaction to this package for rollbacking in case the package fails. What I found is a property "TransactionOption" which should be given "Required". Am I right ? And I have set TransactionOption for the package as "Required" But the package fails when I executed. What all steps should I do for setting TransactionOption "required" ?

In detail, My package contains 4 control tasks. One of them is a Data Flow Task, containing Lookup Tasks.

I got the following error "[Execute SQL Task] Error: Failed to acquire connection "SQLConnectionMgr1". Connection may not be configured correctly or you may not have the right permissions on this connection."

When I set the TransactionOption to "Supported", the packages runs successfully.

回答1:

1. Create 2 "OLE DB Connection managers" both connecting to the same database. On the properties of one of the "OLE DB Connection managers" set the 'RetainSameConnection' property to true.
2. Then create 3 "Execute SQL Task" and connect them with the "OLE DB Connection managers" were the 'RetainSameConnection' property is set to true.

3. Add the following statements on there respective Execute SQL task

   • BEGIN TRANSACTION
   • COMMIT TRANSACTION
   • ROLLBACK TRANSACTION

回答2:

It sounds like you might not have appropriate permission to use the Distributed Transaction Coordinator (MSDTC) service which is required to utilize transactions in SSIS.

See this article on Transactions for more information: http://www.mssqltips.com/tip.asp?tip=1585

Also you might need to look more into how the MSDTC works in relation to SSIS to get your issue resolved.

回答3:

To enable Trancaction is ssis you need to check below prerequiste

1, Distributed Transaction Coordinator (MSDTC) service should be started in the machine, if its local machine.

2, if you are using has a client (server machine-->user machine) then MSDTC enabled in both machine with security option of NETWORK DTC Access (Allow Remote Clients, Allow Inbound, Allow Outbound) under Component Services --> Computer Properties --> MSDTC tab --> Security Option

3, Sometimes Firewall from any one machine or both can be blocked of network DTC so you need to disable/uninstall.

Errors: Cannot Acquire Connection Manager

Solution: check the step 1 if you are user of local machine, if you are client user check step 2 and 3

Then asusual change the Trancaction Option as required in the properties of package level or container level as you required and check.