This one's kind of hard to answer as by the time I get done with 'em the original modules aren't always all that recognizable. Well, that and I often only use part of the material...
Forgotten Temple of Tharizdun is always good value. I've run this several times and almost certainly will again.
Secret of Bone Hill is excellent. I've run it once and played it twice.
A2 Slavers' Stockade - I've run it and it played much better than it read.
Forge of Fury might be good if I ever get to run all of it - my crew turned around and left halfway through. It was good when I played it.
I have good memories of running G1 Steading of the Hill Giants but it's been a very long time and I'm not sure how rosy-coloured my glasses have become.
But probably the two greatest not-written-by-me* adventures I've ever run are a couple of my friend's homebrews. One of these days I hope I can talk him into publishing...
* - those written by me are anything but great.
Lan-"the best adventure is always the current one"-efan
I6 - Ravenloft & WG5 - Mordenkainen's Fantastic Adventure are the two that really stand out in my mind as being the most enjoyable modules that I have run.
I think OA7 Test of the Samurai has an interesting underlying premise, and some good encounters on the way to its resolution. (I ran it converted to Rolemaster and with more than the odd tweak here and there.)
I ran a variant of the Freeport Trilogy in the same Rolemaster campaign, for high level PCs, and enjoyed it, although I changed the climax of the third module to a significant extent (inspired by some stuff from Warren Ellis's old Dr Druid comic).
I think Bastion of Broken Souls, for 3E, has some good ideas in it but they are utterly spoiled by the execution - every PC is presented as a combatant only, when the module (from my experience, again running it in the same RM campaign) is much more interesting if the players actually get to engage the story.
I've used bits and pieces of the D series as vignettes over the years, but have never tried to run it as a linear series of modules. And talking of vignettes, I can recommend Eden Odyssey's Wonders out of Time d20 vignette collection. I've used 3 of them so far (converted to 4e), and they've all been interesting.
The Secret of Bone Hill
Of Sound Mind
The Gates of Firestorm Peak
Return to the Tomb of Horrors
Keep on the Borderlands
The Lost City
Expedition to the Barrier Peaks
Those are the ones that spring immediately to mind.
Two that really spring to mind are Scepter Tower of Spellgard, and The Veiled Society. however, BOTH of those adventures were really fun because I changed large portions of them to fit what I was aiming for. I think I only used approximately half, if not less, of either.
Isle of Dread would probably be the one I had the most fun running, but even then, it was significantly changed "out of the box".
In the 80s, it was Judges Guild "Caverns of Thracia".
Nothing in the 90s.
In the 2000s it was a combined Necromancer Games Campaign... I put "Lost City of Barakus" adjacent to Quail Valley ("Vault of Larin Karr").
I6 - Ravenloft & WG5 - Mordenkainen's Fantastic Adventure are the two that really stand out in my mind as being the most enjoyable modules that I have run.
What adventures do I consider great and remember fondly from my past.
Expedition to the Barrier Peaks: I really enjoyed this module. The mixing of sci fi and fantasy was well done. I always enjoy modules with unique items and monsters I don't know about. The module that introduced the vegepygmy I believe. I liked using those black plasma blasters in the module. At the time they were a really dangerous weapon.
White Plume Mountain: Similar reason as above in that there were items, creatures, and situations that were very unique which made the module fun.
Tomb of Horrors: This module I enjoyed reading more than playing, though it was fun to play. Very creative module. A real player killer and make you paranoid module.
Keep on the Borderlands: Just because this is sort of the granddaddy of modules. One of the very first memorable modules and a sort of measuring stick for how to design a dungeon crawl.
Against the Giants Series: How can you not enjoy a war against giants? It was a blast. Assaulting giant forts and imagining yourself as a group powerful enough to crush giant armies was a great fun.
Temple of Elemental Evil: I only got to the end once. But it was the first mega-module. I played it many times trying to finish it. Good story and goal. Interesting dungeon with endless hours of play.
Slavelords Series: Never finished this series in order, though I think I played every module. Great idea. Hard to run due to the number of modules and situations. I loved tracking down the various slavelords. That was fun.
Isle of Dread: First island adventure module. Big fun wandering around an island in a sandbox style. No railroading at all in this module. The entire module was centered around exploration for the sake of exploration. I really enoyed it.
The Randall Morn Series: I can't remember the name of this module series, but I remember the modules. Set in the Forgotten Realms. You get to involve yourself in the liberation of a dale and help Randall Morn retake his throne from the Banite-worshipping Zhents. It was pretty fun. One of the better story module series I played.
The Fate of Istus: I ran this with just a single friend. He DMed. We both ran two characters. Great story module. One of the few modules I had been through where success or failure caused a real change in the world. And when my paladin bested that polymorphed red dragon in single combat, that was awsome.
Hellbound: I think this is the name of the module. You start off going to Sigil. You end up in this conflict with the Yugoloths as you try to save this fallen angel that digs holes in the multi-verise that allows demons, devils, and yugoloths to teleport. This module was a blast. And to succeed and have a real effect on the game world is always fun. The players received a reward worthy of what they accomplished at the end of the module. It had great scenariors for combat as well as roleplaying. This was a really, really fun module.
Ravenloft: The first well-done vampire myth module I can remember. This module was good fun. Gypsies, a cool and formidable vampire, perfect dark setting. A real creative module that was fun to play as well as read. Count Strahd Von Zarovich is one of the most memorable D&D villains for the old timers that went against him. Some of those traps in his castle were nasty.
Into the Mirror and Dungeonland were sort of fun too. A designer's attempt to do an Alilce in Wonderland style of adventure. It wasn't too bad. I do enjoy unique module ideas.
I aslo enjoyed the Egyptian themed adventures mostly because they were Egyptian themed and fairly well done. I don't remember them quite as well as the other modules. I seem to recall they were some of the first modules to use a mummy as a main villain, which was fitting and interesting.
War of the Spider Queen: I enjoyed this module. Fairly well-designed and interesting story. One of the better drow modules. Never did play through to the end, but I enjoyed what I did play of it.
Kingmaker: This AP is already a classic for me. One of the most memorable modules I've run or played in. I hadn't DMed a long-term campaign in a good ten years or so. I hadn't had much desire. I was going to do The Red Hand of Doom and started it. But it didn't pique my interest enough to really get into running it.
But this Kingmaker adventure path is addicting to run. It really offered something new to both DM and player. It gave the DM simple, but fun, kingdom building rules. It gave players a chance to truly build a kingdom, armies and all. Throw in the unique items and interesting plot with creatures not normally used very often, and it makes for a classis series of adventures on par with anything I've ever played.
I'm also enjoying Rise of the Runelords as a player. This series of modules also takes the players off the beaten path of standard adventuers. Very unique storytellinge scenarios and interesting adventure design choices. Really immersive and interesting series of modules. Though a few of the encounters were pretty annoying for your level. They were not at all easy to handle. In fact, near impossible to handle, and not in a fun way. Those encounters hurt the modules a little bit. But the story in the modules was strong enough to overcome the annoyance I felt with the encounters.
The entire group almost getting killed by a tiny female imp with a tiny +1 returning dagger is pretty friggin irritating. We're a barbaric war party dedicated to Gorum and this little female imp is tearing us up from the air with a tiny dagger. We couldn't hit her and she kept turning invisible and regenerating every time we came close to killing her. She took down the priest and was slowly wittling us all down. We had to run from her the first time. It was friggin embarassing.
Second Darkness: First module in Second Darkness was pretty fun. Working at the Inn was interesting. Later modules weren't quite as fun in play, but the overall story was an interesting read. So far I'm impressed with Paizo's adventure paths. Best I've see since the days of TSR. That's what Paizo feels like to me. It feels like TSR reborn. I think Gygax would love the creativity at Paizo.
We are in the "General" forum? Good. So the MerricB for being the most enjoyable and successful published adventure from a GM's perspective goes to:
Shadows over Bögenhafen (WFRP)
I've run it thrice and all groups managed to save the town in the very last moment, not without sacrificing at least one of them. While the players were very free in deciding what to do and in which sequence, I still had a lot control over the situation. Not a bad feat for a published adventure!
Apart from this absolute winner, I'll honourably mention three more modules:
Masks of Nyarlathotep (CoC)
The group and me had a very satisfying time with this monster of a campaign, although we didn't finish it. While the single adventures are less than spectacular, the a ambiance of MoN is hard to beat.
The Sinister Secret of Saltmarsh (AD&D, 1e)
I can't even remember how often I've run this using different game systems. Once, at a con, I used it even though I had forgotten it, running completely from memory. I especially like it for the sequence of varied scenes (haunted house, secret cellar, grottoes, ship) and how naturally one thing leads to the next, without the DM having to steer the group.
Against the Cult of Reptile (AD&D, 1e)
This was the very first adventure I had run without playing an RPG before. I still have fond memories of the unbridled roleplaying which took place in Orlane, the subdued look on the players' faces as their characters trotted down the dank, dark corridors of the troglodytes' lair, and the horror when three of them missed their Resurrection rolls after they had hardly slain the naga.