sql server dts: DTS, Transactions and MSDTC Error/Problem -- sql server dts

DTS, Transactions and MSDTC Error/Problem

Joe Smith
7/7/2003 4:29:50 AM

sql server dts:

I'm attempting to use Transactions within a DTS package
but am experiencing problems and receiving the following
error:

"Package Error

Error Source: Microsoft Data Transformation Services(DTS)
Package

Error Description: The Microsoft Distributed Transaction
Coordinator (MSDTC service) is not available.)"

MSDTC is NOT running on the server as I did not believe
that transactions within DTS required this service. Is
this the problem ? If so, is there any way of controlling
transactions from DTS without using MSDTC ? (I am only
using a single server on which all dts packages execute).

I've simplified my package to only contain a single task
and connection but still get the error. Any help would be
greatly appreciated. Details as below:

SQL Server 2000, SP3, Windows 2000 Server,
SQL Server 2000 Dev Edition Client, SP3, Windows XP

The package contains the following:

1 x MS OLEDB Provider for SQL Server connection
1 x Execute SQL Task with "SELECT 10 as 'RetVal'" as the
SQL

Package Properties:
Use Transactions = True
Commit on successful package completion = True

SQL Task Workflow Props:
Join Transaction if present = True

Thanks,

Joe

Re: DTS, Transactions and MSDTC Error/Problem

Gert E.R. Drapers
7/9/2003 11:31:14 PM

Yes this is the problem, MSDTC need to be running in order to create
transactions that same tasks in a DTS package.
No not if you need to span transaction scope between connections and task in
a package, you otherwise are limited to use transaction per connection and
they are not orchestrated inside on transaction, which is what DTS tries to
achieve.

GertD@SQLDev.Net

Please reply only to the newsgroups.
This posting is provided "AS IS" with no warranties, and confers no rights.
You assume all risk for your use.
Copyright © SQLDev.Net 1991-2003 All rights reserved.

[quoted text, click to view]