Civ 5 is real solid, I really enjoy it, but I also find it a bit too passive. Shogun 2 is probably good as well, though I've never really dared to get close to those Total War games.
The Heroes of Might & Magic series is magical (huehue), but I don't know how much of it is nostalgia. HOMM 3 is definitely the way to go as someone mentioned above but I think the latest(?) installment is probably the second best.
I agree with dudeglove about XCOM, that game is fan-fucking-tastic. It made me really appreciate those types of turn-based combat strategy games (for which I should also recommend Shadowrun Returns) and I think I got into the Fire Emblem series via that game as well.
Speaking of other turn-based combat strategy games, I've heard good stuff about Valkyria Chronicles which came out for PC recently, but I don't know much about it other than that.
When it comes to Real-Time Strategy games, I think the Starcraft 2 campaign is absolutely wonderful to play for it eases newcomers into the game very nicely and not all of the missions are just "build base, kill enemy".