Flash and its associated players are all the rage (actually have been for quite some time), except for with the iPad that is. Obviously, you can embed video into your Drupal site in a number of ways.
However, if you're seeking to do REALLY cool things with video then Flowplayer API is definitely the way to go. It's going to provide the greatest level of customization (that's customization that YOU'LL have to do), with the lowest amount of overhead, of the other modules I've worked with. You'll have to get into the PHP of your theme to implement, but once you start to learn it, you'll really start to think about radical things like Views based videos series driving Flowplayer to create dynamic playlists and other cool whiz-bang features.
UPDATE: In the video, I mention that Flowplayer is becoming the most popular open source flash player. This is debatable. I should have said it's "my" favorite player. Here are some trends and insights regarding the two most popular players.