My experience of driving is mainly torque converter autos of bland family saloons (4-speeders) , a brief period with my own manual old-banger of a renault clio plus the odd manual hire cars.
I had always been very excited about getting a hire car because I know it will be a manual and I shall have fun! In older cars, manuals will usually be more efficient as well if one drives it properly.
But as auto transmissions improved massively I am now at the point where I can appreciate a good auto and prefer it over a manual. However, that did not prepare me to be blown away by the grand daddy of them all, the DSG.
What happened was I hired a car for a week for a road trip round the Scottish highlands. So, I was dreaming whether it would be a Golf (mm not bad!) or maybe a Focus (nice!) or Astra (well... still a manual trans..). I had great expectations of driving round some good roads, in a smallish car with a manual transmission.
Imagine what they gave me

A VW Sharan MPV (I booked a Golf or similar car) 2.0 diesel with a 6 speed DSG!

Well, there goes my 'dreams' and I am stuck with an MPV for a week driving about some excellent roads.
What happened was over the course of the week I had with the car, I realised what an amazing gearbox it is. It shifts so fast, so smoothly just like the best auto while giving none of the torque converter lag.
I am sold. Yes, after driving of all things, a big MPV with a DSG gearbox, I absolutely adore those things. (I must add that I was very impressed by the car as well. Averaged 46 UK mpg compared to the 45 I got more recently on a 1.4 petrol, manual trans Astra) DSG must be the future...
Danoff puts it spot on
For most DSGs you can put them in automatic mode and then (if you have a good one) you have the best automatic you've ever driven. Then if you want to control the gear, they're the best manual you've ever driven.
As a final note, there will still be manual transmission cars but they will be a niche. And yes, in older cars too.