Twilight Princess is great, for the most part, and actually has quite a few memorable moments for me. Lake Hylia, Zora's Domain, Hyrule Castle Town, and the overworld as a whole are all very well realized. Out of all the Zelda games released in the 3D era, I think Twilight Princess actually sticks out the most for me in terms of its environment design. Sure, it's not really that original, but some of the scenes and locations are just really cool, and packed with great art and huge amounts of detail (for a Wii game, anyway).
While I found the plot to be, for the most part, kind of barren and uninvolving, there were a few moments that struck with me. The one wimpy kid slowly following in Link's footsteps to become a warrior was a nice subplot and character arc that wasn't shoved down the player's throat, and resonated precisely because of that. I also really liked Midna as a character - she was well-written, mysterious and generally just entertaining to have around, unlike some of the other Zelda companions in the past, and when she gets her powers drained (or whatever it was, don't quite remember) during the middle section of the game, with that really nice piano remix of the theme playing in the background, that really struck a chord. There's a simple dichotomy in having a genuinely likeable and helpful character suddenly become helpless and dependent on the player, and it makes you appreciate that character more in the long run as an actual "person" rather than just a plot device. The way the soundtrack and the tone of the game reflected that really helped the moment stand out.
Ultimately, Twilight Princess is kind of just an Ocarina of Time clone, but I think it does a way better job in creating interesting characters, environments and so on. I really don't mind the fact that it borrows so heavily if only because it so frequently improves upon the original. Ocarina of Time is still excellent, of course, and its dungeons were a both more memorable and better designed, but everything else around it looks quaint next to Twilight Princess' genuinely epic scale.
Also, Skyward Sword looks like it's going to suck nuts. Art style is okay, but the focus on the gimmicky sword fighting (which looks like it will be fun and challenging for about 5 minutes), the motion controls for everything (that new beetle thing = remote control boomerang, big fucking deal), and the downright tame puzzle design seen so far (hit the red switch with the red item? shit, this is difficult!) really has me worried that they're going to pull another Phantom Hourglass, i.e. dumbed down shit because Nintendo feel Zelda is too difficult for the masses to grasp, and would prefer high-impact gimmicks over quality gameplay. The last few Zelda games have suffered from that problem, and Skyward Sword looks like it's shaping up to be just like them.