I remember Morrowind modding those mods added to the game and gave the player the reward of more story and more development of the character and the setting. In contrast Oblivion a game that is not as fleshed out the modding community has yet to make a Cyrodiil that is deep like Daggerfall, Morrowind and Skyrim.
Awww, no mention ofhttp://www.moddb.com/mods/integration-the-stranded-light yet?... It pretty much does exactly that. Personally I found that a lot of Oblivion quests, and even a number of quest mods were little more than dungeon crawling, and fetching loosely pieced together by someone just saying : "go here, do this." The lack of choice, recognition, possible solutions, strategy, intrigue, etc. pretty much destroyed any story, meaning, or achievement behind it. At least Integration offered many diverse, and quality quests with motivation, character, and intrigue leading it. Personally for me Integration is the type of mod that so enraptured and out did the core game that many years from now if I remember Oblivion, I'll remember Integration, and not the mediocre game that came before it.
I've only played a few David Brasher mods, They were okay... a bit lacking in story, and character maybe, but okay... At least in White Stallion Expanded the Black Bow guy had some background.
Personally I prefer the http://www.tesnexus.com/modules/members/index.php?id=125368
Sancre Tor, and Dark Brotherhood Chronicles look good, first on my list to try when I start playing Oblivion again.
Speaking of Overhauls, there is Nehrim which featured it's own world and story. As an in-between there's Windfall which had a new worldspace connected to the vanilla game, and was focused on story unlike most of the other new world space mods where it's largely "ooo, pretty, pretty!!"
Windfall and the Simyaz mods probably fit into that "very old mods that people always mention" category, and yes I too find that somewhat annoying and sad (
at Lost Spires), but the others mentioned are all fairly new, or at least had big updates in recent times.
Though if you really wanted to be disappointed in a lack of quest mods.... Get Fallout 3. Fallout New Vegas got a bit more good quest mods thankfully.