Does Sql Server 2000 support SSIS transactions?

2
votes

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

Tried:

  1. Checking if the DTC service is started in both machine, the client and the Database Server and they are both started.
  2. 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
  3. 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?

2
sql-servertransactionsssis
before editing you used several question marks where only one was need e.g. Does Sql Server 2000 support SSIS transactions????? - marto
Could you elaborate on "On the 2000 SQL Server, there is no way to access the above components"? I've certainly had to make DTC changes for SQL Server 2000 before... - Damien_The_Unbeliever
Made changes to the question, as requested - Brian Paul

2 Answers

5
votes

SSIS was first released with SQL Server 2005 - so it is not supported on SQL Server 2000.

Look at DTS which was what SSIS replaced.

Use DTCPing to ensure that distributed transactions are setup correctly on both servers.

0
votes

For:

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 )

Stop before that point. Right click on My Computer (inside component services, etc), and choose Properties. There is an MSDTC tab. Inside there, you can open "Security Configuration...", and in there you'll find the Remote Clients, Inbound, Outbound, etc.