You've asked several different questions, so here goes:

1. How effective is it? It is a lot more effective in cameras that have it than those that don't. Not trying to be a smarty-pants, but since most manufacturers adopted automatic self cleaning systems, the number of complaints regarding dusty sensors went down by several orders of magnitude. See labnut's answer to this question. For how to utilize it effectively, see this question.
2. Compared to a blower? It depends on the type of dust or other substance. Normal dust is fairly easy to remove. The problem with using blowers is that they often introduce more dust into the system than they remove. Always use a blower with a filtered intake valve that is opposite the exhaust nozzle. Otherwise you are just swishing dust back and forth. Dust that has been damp is a little harder because it causes the dust to be more strongly attached to the surface it lands on. Other substances, such as the lubricants used inside the camera body, can be a gooey mess that neither an automatic cleaning system nor a blower can remove.
3. Can you overuse the automatic dust removal system and damage the sensor/mechanical parts? With any mechanical device there will eventually be enough wear for the device to fail. But if there was a problem with the use of automatic dust cleaning routines causing damage to cameras, we would have heard about it by now. They've been pretty widespread for about half a decade now. One would assume they will last longer than other parts, particularly the shutter mechanism which is usually the first internal part to wear out.
4. How does it work? Most systems use a piezo crystal ultrasonic vibration of the IR filter in front of the sensor. They vibrate at around 35-50K Hz. Olympus invented it, but now Leica, Panasonic, Canon, and Nikon use similar systems. Other manufacturers use sensor shifting. The sensor itself is shaken at about 100 Hz, but the length of travel is much further. Konica Minolta developed it. Sony and Pentax now use this method. Both systems usually include a coating that is negatively charged, just as most dust is. This causes them to repel each other.
5. What happens to the dust? When vibrated off the IR filter, it should fall down to the bottom of the sensor where a dust trap collects it. We think of dust as not being affected by gravity, but this is a false assumption. Dust will fall unless the force of air currents around it creates more frictional force than that of gravity, unless there is an electrical charge acting on the dust that is stronger than gravity, or unless a combination of both air and static charge is stronger than gravity. Air molecules that are affected by Brownian motion are much smaller than dust particles. Inside the mirror box there is not a source of moving air to suspend the dust in the air for any length of time. Some designs actually use air motion to help the dust move towards the trap below the sensor. They generate this very weak air motion by the designed shape of moving parts in the dust cleaning system.
6. What's the most effective and safe way to clean a sensor? There isn't one. There are more effective methods, and there are safer methods. They are generally inversely proportional to each other. The methods, in order from lowest to highest risk factor are: Automatic dust removal system, air blower (with a filtered intake), electrically charged brush, and wet cleaning systems that use swabs and cleaning fluid.

http://en.wikipedia.org/wiki/Dust_reduction_system#Sensor_shifting