On a big screen, displaying true 1080 resolution, the difference between HDTV source material and upconverted DVD's is obvious. Many people have only really watched movies that are broadcast in HD, but are in fact just DVD's upconverted. True HDTV is shot in 1080 video, and it is breathtaking.
As Kenny pointed out, upconverted 480p is still just 480. Many DVD's are not even close to 480, due to compression. If you watch a DVD player with DVI output and upconversion, the real benefit that you will see is just due to the DVI connection itself. Other differences may be due to the quality of the player itself.

On a good monitor, it all looks great.

On the right screen, you can also see a difference between component input and DVI input, but the difference is slight. The main difference is that component is analog, and subject to the artifacts/interferance that DVI is not.

Many people cannot see a difference between 720p and 1080i, but many also find 1080i to look "smoother". Again it probably depends on the monitor.