The particular unit I have has an FM modulator/broadcast circuit built into it, so I use that when I am in hotels, friend's houses or anywhere else without a direct input.
It does get you music, but the sound quality is definitely worse than a direct line by a large margine. How large a margin depends on a couple of factors, the most important of which are the quality of the modulator and the tuner you're receiving the signal on. Dynamic range is usually greatly compressed and frequency response is more limited as well. You're also open to many forms of interference that are not present or are more limited with a direct line in.
FM Modulators are a great way to go if you have a rental or leased car where you don't want to do any damage or a complex install you'll have to remove later. Failing that, you want a direct input. MP3 and most other digital forms already have some compression or limiting in them, so every transformation the music makes degrades it again. The fewer devices in the chain, the better you are.
Aux inputs are making a huge comeback in car stereo now due to the popularity of MP3 players. Some OEM units even have these inputs, and BMW has integrated iPod controls into some of their car stereos. Pretty neat!