I am running an SSIS package, that pulls data from two SQL Servers, One is a SQL Server 2008 and the other is a SQL Server 2000. I have been trying to implement transactions in SSIS lately.
The problem i am facing now is, the package always fail reading the SQL Server 2000 when transaction level in the packages is set to required, with an Error
The Acquire Connection manager failed with error code 0xC0202009
- Checking if the DTC service is started in both machine, the client and the Database Server and they are both started.
- On the Client, went to Control Panel >> Component Services >> Computers >> My Computer >> Distributed Transaction Coordinator >> Local DTS (Right Click, then Chooose Properties ) >> Security Tab ( Allow Remote Clients, Allow Inbound, Allow Outbound ) are all selected
- On the 2000 SQL Server,went to Control Panel >> Component Services >> Computers >> My Computer >> Distributed Transaction Coordinator >> (No Local DTS) and right clicking will not give me properties of the DTC, cant find a way to access the options ( Allow Remote Clients, Allow Inbound, Allow Outbound )
Now I was wondering does SQL Server 2000 support SSIS transactions?
SSIS was first released with SQL Server 2005 - so it is not supported on SQL Server 2000.
DTS which was what
DTCPing to ensure that distributed transactions are setup correctly on both servers.