Got the Orange Box on PC. Thinking only of the games with achievments enabled:

Never had a problem with the achievements for Ep2, though I considered a fair portion pointless given that you get them just for getting so far into the game. By the end of your so many hours you've automatically racked up a third or so of the available achievements. So those were perhaps a little niggle but I just ignored them.

Other achievements such as the one which requires to carry the garden gnome through 80% of the game were incredibly frustrating to do but very satisfying once completed. And I sat back and laughed myself silly when I discovered the Rocket Man solution ;)

Portal - again, easily got achievements just for getting so far in the game. Plus the existing other challenges are a little imprecise - wouldn't it just be better to publish your fastest run/least steps/least portals for the master levels?

Team Fortress 2 - Here achievements are really worth something and often difficult to achieve. Plenty of oppertunity to expand upon the selection in future patches, too - particularly more skill based challenges. A lot of them so far encourage you to fully explore the options presented to you, so you at least play every class and every level once.

Overall... I like achievements; they have their place and that is in puzzles and MP games (like in Battlefield 2). Stories like HL series and BioShock really aren't suited to them.