Is possible to run DTS with Transform Data Task, which have source and
destination connections on the same server?

This task copy 1:1 table from source database to destination database
(without ActiveX script).
When this databases are on a diffrent server than DTS run correctly.
When environment for DTS is on the one server, than I see error:

" Step Error Source: Microsoft Data Transformation Services (DTS) Package
Step Error Description:Unable to enlist in the transaction.
(Microsoft Data Transformation Services (DTS) Package (8004d00a):
Connection 'Oferta_doc' for Task 'DTSTask_DTSDataPumpTask_1' does not support
joining distributed transactions or failed when attempting to join. Unable to
enlist in the transaction.
) (Microsoft OLE DB Provider for SQL Server (8004d00a): New transaction
cannot enlist in the specified transaction coordinator. )
Step Error code: 8004D00A
Step Error Help File:sqldts80.hlp
Step Error Help Context ID:1100".

When DTS have 2 steps (first is "Execute SQL Task" for delete records from
destination table) then first step is correctly, second generated error:
“Step Error Source: Microsoft OLE DB Provider for SQL Server
Step Error Description:Transaction context in use by another session.
Step Error code: 80040E37
Step Error Help File:
Step Error Help Context ID:0”

DTS with a few "ecxecute SQL Task" steps and without "Transform Data Task"
work correctly.

Could anyone help me?

Best regards
Zbyszek Bieganski

Hi Zbigniew

"ZbigniewB" wrote:

This could have several reasons. The error occur on the first step
requiring a transaction, what simple sql tasks doesn't by default.
The Server must be able to reach the msdtc via TCP/IP even on itself.
If you have a firewall or similar port filtering or security restrictions,
then this could be prevented.
The result may vary depending on if the server is owner or joining part of
the transaction.
See the folowing articles as a starting point

http://support.microsoft.com/kb/191168
http://support.microsoft.com/kb/839279

If you need further help please add detailed information on your systems
incl. OS, SP...

Helge