I'm looking to use DTS to delete and reload some volatile data from
one of 40+ Informix DB's. For a number of reasons, DTS appears to be
the right choice for this exercise.

Running the DTS package designer locally, but connecting to a remote
server, any attempt to enable transactions on a package fail.
Simplified steps to reproduce:

1. Run Enterprise Manager on your local machine
2. Pick a registered server or add a new server (should be remote)
3. Drill down to Data Transformation Services | Local Packages.
4. Add a new package
5. Add a new SQL Server connection:
- Should default to the same server that you drilled down to
- Choose Northwind database
- I've tried this with both Windows Authentication and SQL Server
with similar results.
6. Add a SQL task to the package (something like "SELECT * FROM
PRODUCTS")
7. Run the package to ensure that it succeeds.
8. Bring up "Workflow Properties" on the "Execute SQL Task" icon
9. Click Options, select "Join Transaction If Present" and "Rollback
Transaction on Failure"
10. Re-run package - I get the following error:

"""
Unable to enlist in the transaction
Connection 'Microsoft OLE DB Provider for SQL Server' for task
'DTSTask_DTSExecuteSQLTask_1' does not support joining distributed
transactions or failed when attempting to join. Unable to enlist in
the transaction. New transaction cannot enlist in the specified
transaction coordinator.
"""

I don't have admin access to the remote DB, but I'm sure that DTC is
running on that machine. If I remove any/all TX settings from the DTS
package and change the SQL to read:

begin distributed transaction
select * from products
commit

then the package executes without error. I'm assuming that this SQL
source is running under the remote SQL Servers process, which can
enlist in a remote transaction; whereas my DTS package is running on
my local machine, and it cannot enlist in a TX.

Finally, I used the VB.NET CreateTransaction sample code to test the
transactions in general. Using the same SQL server as I used above, I
was able to create, execute and commit a transaction without issue.
I've also tried this VB.NET code test using a SQL Server authenticated
connection, as well as integrated security.

-- Jupe

Works for me. You are sure the connection is to SQL Server? You are sure
MSDTC is started? Are you Service Packed?

--
--

Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.SQLDTS.com - The site for all your DTS needs.
www.konesans.com - Consultancy from the people who know


"Jupe" <jupiterdude (AT) hotmail (DOT) com> wrote

I am sure it is SQL server. MSDTC was started, and working. SP level is
8.00.818 (SP3).

It appears to be working when pointing at virtually any other SQL
server in the enterprise. We will be restaging the finicky server ASAP.

Thanks for your input.

-- Jupe


Allan Mitchell wrote: