SSIS Urgent Help Please

SSIS and Import/Export
1

Guys,

I need urgent help.
I have simple SSIS Package

  1. Using Execute SQL Task
  2. Data Flow Task (Inside Data Flow reading Flat file and dump into SQL Table)
  3. Using Execute SQL Task (Update last run date Field in Master Table)

Here I am using Sequence Container, the reason I want to use Sequence Container because If any task fail everything rool back. So my Sequence Container Configuration is
Right Click on Sequence Container and hit Properties/TransactionOption = REQUIRED
Right Click on Execute SQL Task and hit Properties/ TransactionOption = SUPPORTED
Right Click on Data Flow Task and hit Properties/ TransactionOption = SUPPORTED
Right Click on Execute SQL Task and hit Properties/ TransactionOption = SUPPORTED

After I execute my Package, I am getting error when first task execute "[Connection manager "sansql05.ETL"] Error: The SSIS Runtime has failed to enlist the OLE DB connection in a distributed transaction with error 0x8004D025 "The partner transaction manager has disabled its support for remote/network transactions.".

However If I change my Configuration seeting to
Right Click on Sequence Container and hit Properties/TransactionOption = REQUIRED
Right Click on Execute SQL Task and hit Properties/ TransactionOption = NOT SUPPORTED
Right Click on Data Flow Task and hit Properties/ TransactionOption = NOT SUPPORTED
Right Click on Execute SQL Task and hit Properties/ TransactionOption = NOT SUPPORTED

The Package working fine without any Problem but it is not Rolling back if any task fail.

Please guide me where I am doing wrong. Thank You It's a urgent help Please.

2

The first option is correct but you need to ensure that the distributed transaction managers on all machines involved are enabled and support remote/network transactions.

3

Thank You So much gbritton for your reply. Could you please tell me how I can is this option is enabled or not.

Thank You.

4

Ok here I found on Internet

Use this for windows Server 2008 r2

Click Start, click Run, type dcomcnfg and then click OK to open Component Services.

In the console tree, click to expand Component Services, click to expand Computers, click to expand My Computer, click to expand Distributed Transaction Coordinator and then click Local DTC.

Right click Local DTC and click Properties to display the Local DTC Properties dialog box.

Click the Security tab.

Check mark "Network DTC Access" checkbox.

Finally check mark "Allow Inbound" and "Allow Outbound" checkboxes.

Click Apply, OK.

A message will pop up about restarting the service.

Click OK and That's all.

My question is, Is there are any problem if i Enable this? or any other issues...

5

No problems BUT it has to be enabled on all machines participating in the distributed transaction, not just on your own box