2004-12-02 19:17:51 UTC
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
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
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
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.