We have a package where the source and destination are on the same server (SQL Server 2000). The package populates data into a table. When attempting to run the package we received the error
"Error source : Microsoft OLE DB Provider for ODBC drivers
Error Desc : Connection 'Microsoft ODBC Driver for SQL Server' for Task 'DTSask_DTSDataPumpTask_4' does not support joining distributed transactions or failed when attempting to join. Unable to enlist in the transaction"
I have researched and researched to no avail. Everything I read says to make sure the DTC is started and I have verifed that is running. In the component services section the security is set to Allow Remote Clients and Remote Administration, Allow Inbound and Outbound. No authentication required is checked as well as enable TIP transactions and enable XA transactions. The DTC is running under the Network Service account. So everything is in place.
The workflow properties were set to join transaction if present and rollback transaction on failure as well as execute on main thread. The package fails everytime it tries to populate the table. This is all on the same server so I don't undrestand why this fails but it runs in the production environment where two different servers are being used. It can't be the DTC because it is started. At this point I think there's something wrong with the package. Since the error says it's failing to enlist transaction, does Join transaction if present have to be checked? What are the implications of unchecking join transaction if present? Can someone please assist? Thanks in advance.