So there's (at least) four different types of transmission with flappy paddles on it -- a traditional torque converter automatic (tiptronic is this), a CVT (low end Audi and VWs have this with flappy paddles to select the ratio), a traditional manual with a single clutch that's all driven by solenoids (Ferrari "F1" and the early BMW SMGs), or the dual-clutch, dual-input-shaft transmissions that are becoming widespread now (DSG, PDK, s-tronic, etc).
Personally, I hate the disconnected feeling you get with a torque converter, so tiptronics are of no interest to me. CVTs tend to be more about efficiency than fun, I've never driven one that didn't suck.
That leaves the single and dual clutch flappy paddle gearboxes. From a "fun" perspective, I think these are roughly equivalent to each other, the differences is that the single clutch ones are great on the racetrack, but tend to be very harsh and jerky around town. For whatever reason, programming them to do both seems to be hard. The dual-clutch flappy paddle ones, OTOH, are apparently easier to set up to do both, which is why they're taking over. (Except in F1, where they're banned by the rules).
I recently replaced my manual-transmission B6 S4 with an s-tronic, dual-clutch, flappy paddle C7 S6. I've had it about 3 months now, and I'm still somewhat torn about it. I like the speed at which it shifts and I like the rev-match downshifts it does when you pull the left paddle, but the problem is the control software. It's got 3 modes you can operate it in: "D" (which is fine for driving in traffic, but is slower to downshift than I'd like), "S" (which downshifts faster, but also doesn't upshift when you get to a cruising speed), and "M" (manual). In theory "M" should fix everything, but the problem is that it still automatically upshifts if you hit the rev limiter. This means that if you're trying to do a manual acceleration run and let it go too high it will shift to 2nd, and then when your shift command comes in 10 milliseconds later it shifts to 3rd and it's now in the wrong gear. This is a software problem not a mechanical one, and the control software varies a lot by manufacturer, so you'll have to try out whichever car you're thinking of specifically.
So is it as much fun as a manual? For certain types of driving, no, it's not. You don't get the satisfaction of doing heel-and-toe rev-matched downshifts yourself, and it's certainly less engaging than a 3-pedal car. I wouldn't want my Miata with a flappy-paddle gearbox. OTOH, it's a lot more fun (well, perhaps "less unfun") in heavy traffic than a clutch pedal is. It makes cool noises, it shifts fast, and it's almost certainly a faster way around a racetrack (not that I'll ever take the S6 to the track, that's what the Miata is for)
If the S6 had been available in a manual and I'd chosen to buy the s-tronic, I think I'd be regretting that choice. It's not though, the car (and all of the other cars in its class) is simply not available with a manual, so it wasn't a choice between a manual and an s-tronic, it was between an S6 and a totally different car. To me, overall, the s-tronic diminishes the appeal of the car, but not enough that I wish I'd bought something else.