To my opinion it all comes down to the used source material on the DVDs.
And the used chipset of course too.
Just take the "Lord of the rings" on DVD f.e.
Upscaled on a proper system (I do that with a Panasonic BD player which manages upscaling quite well imho) the outcome is really remarkable! Still no comparison to the way higher resoluted BDs, but still quite an enhancement over "standard DVD" experience.
When I take the "Lord of the Rings" DVDs and play with the HDMI resolution and switch between 480p and 1080p i can see a hell of a lot of improovement in picture detail there!
Whereas if I do the same with those quite awful TNG-DVDs, all I can see is that the blurry picture looses some sharpness, but that's mostly it, because the source was crap.
Upscaling can't do miracles to bad source material, but it can hugely improove good source stuff that is just restricted due to the DVD standard resolution.