Writer James Bishop editorializes on Gamasutra on the problems of building a narrative DLC, how BioShock 2's Minerva's Den succeeded and how Dragon Age: Origins' Return to Ostagar failed.
It also doesn't help that many a developer has started planning for downloadable content long before their games release. In fact, the industry seems to be moving toward making larger AAA games into a service platform rather than a standalone affair. This doesn't necessarily have to be a bad thing but this kind of planning can lead to excluding content that should otherwise be present in the retail version for much later.
As an example of exactly this, the Return to Ostagar DLC for Dragon Age: Origins isn't much of a worthwhile adventure because it's the conclusion to an introduction and climax from the retail game. Well, the adventure itself is worthwhile, if only to see the finality of it all, but the fact that the conclusion seems to have been purposefully excised from the vanilla game is another matter.
The player is at Ostagar, loses an epic battle that includes the death of some major non-player characters and ultimately ignores the site unless they purchase the content. The battle is left unresolved for anyone who decides to not pay for it. There's not even an ambiguous resolution for those unfortunate souls. Ostagar simply doesn't exist for them past a certain point in the game. Hiding this behind the barrier of payment is almost criminal as it's where the story should have gone naturally.