Hi:

I'm running DTS package where all steps are set to "join transaction if
present" in the Workflow properties. The transaction isolation level in
the package properties is set to "read committed". The distributed
transaction service is on on the server. SQL Server service pack level
is SP4.

When I try to run the package, the first step that joins the
transaction fails with the error:

Connection "PackageName" for Task "TaskName" does not support joining
distributed transactions or failed when attempting to join. Unable to
list in the transaction. New transaction cannot enlist in the specified
transaction coordinator.

Any idea why I might be getting this error? I have gotten this before I
believe before upgrading the service pack but that wouldn't be the
problem here.

Thanks,
Kayda

Hello Kayda,

If the MSDTC Service is running everywhere then what may have happened is
that you have set up an incompatable task to join a transaction. A Text
File for instance cannot enlist in a transaction


Alln

I am facing same problem with my DTS package(same configuration mentioned). I
have assiged DataPumps and DataDriven Query Task to the transaction
and getting same error.
I am completely confused with this error.
--
Regards
Bharat Jariwala



"Allan Mitchell" wrote:

Yes, I also get this error regardless of the type of task. And the DTC
service is running locally on my laptop and on the server. I am running
the DTS package on my laptop. I have not gotten remote desktop access
from the customer to try and run it from the server.

I read somewhere you have to configure the DTC service for "network
access" or something like that. Exactly how does one do that?

Thanks,
Kayda

Hi All,

Best way to maintain transaction across multiple tables insert/update
without using
DTC is that create temp tables at destination and insert data into these
temp table first.

Create stored procedure at the destination server and write all your
insert/update logic for all tables involved inside this procedure.

you can use transaction at destination server using BEGIN TRAN, COMMIT TRAN
and ROLLBACK TRAN in this procedure and maintain transcation.

Am i doing write ? Do we have any other way around ?

--
Regards
Bharat Jariwala



"shop (AT) pacifictabla (DOT) com" wrote:

Hi Bharat:

I think that you make a good point Bharat, but there are a couple of
problems:

1. I want every step in the DTS to be part of one big transaction. If
nothing works, then no SQL step, not Data pump gets executed. Each step
can be part of it's own transaction, but not part of one big one.
2. How do I do what you suggest with the datapump tasks?

Thanks,
Kayda