On general principle I dont like DLC in the first place because it always seems like company's are nickel and dimeing us. Furthermore any DLC that expands on the main story isnt DLC to me, its a piece of the game that should have been in the game at release for the $60 I originally spent.
That said, there are some DLCs that stand out for me. Dragon Age: The stone prisoner (read: shale) was good DLC. You got a new and unique character that you could play throughout the story and who was really a part of the story, not just there like Zaeed from ME2. Shale's addition through DLC is the right way to add a character to the main story
Fallout 3's DLCs like Mothership Zeta were also good DLC IMO. Zeta was basicly a side quest. You could return to explore the areas, they changed as you pushed the story along, and there was actually some real depth to the individual story. Thats what I like to see in DLC, it adds things that dont effect the main story which is the right way to do story driven DLC
As much as DLC is used I think its time for them to start releasing DLC demo's so that we the consumer get a better idea of whether or not we want to play it. Right now about the msot accurate source of information I can think of is watching a play through on Youtube