I had read somewhere about the problem of where and how to power the fans but managed to get mine powered from the old 2 pin power supply on the card. You can do this by modifying the turbo fans 3 pin socket to fit the 2 pin power on the 3870.
Here's an update: I received and installed the Twin Turbo today, in fact I've just booted my Mac. So far so good — that is, I didn't break the card, and there's no smoke or funny smells.

I ran into one significant issue though, that is that
the power connector for the fans is incompatible, just like the original turbo module that Urchinboy was talking about. It's a 3-pin connector with a black, red, and green wire. The original 3870 fan unit had a 2-pin connector with black and red.
I'm not entirely sure how I will be able to get the fans powered. Since the Twin Turbo is essentially the same as the S1 Rev 2 with the turbo module already installed, I think I should be safe running it for the time being with just the passive heatsink. At least I will be able to enjoy some peace and quiet, as the always-spinning fan was starting to drive me nuts.
Edit: here's some pics, first of the board with the original cooler removed. The CPU is naked and the RAM units have new heatsinks installed. Second, the power connectors. The one coming off the twin turbo fans has a 3 and 4-pin connection, assume either can be used, instructions don't mention the fans at all.

It did come with an adapter that I can plug the 3-pin end into, but it's a molex adapter which is useless for the Mac Pro.
I can come back and post some temps after it's been on a while, but as I've no way to check specific GPU temps in OS X, all I can go by is the general temps of everything else. So far, the "Ambient" temp is about the same at around 70ºF and "Expansion Slots" temp is also the same at 94ºF. Does anyone have any idea what the normal safe operating temps are for the 3870? I can check them out later in Windows.
Edit 2: GPU-Z shows current temps at
52ºC, this is idle in Vista x64 that I've only been booted into for several minutes. I think this is normal but not cooler than temps from the stock cooler (I wish I had checked first, but I was anxious to install the Twin Turbo), however it is also silent.
