Put differently:
If you care about connectivity, to the Gamecube or system to system, or the ability to play Game Boy and Game Boy Color games, don't get a DS.
If you want your games to play at the proper framerate, don't get a GBP.
If you want to see what you're playing, don't get an original GB unless you're going to install an Afterburner.
If you don't want to have one of the coolest, most comfortable, and best-looking handhelds
of all time, don't get a Classic NES GBASP.
Me, I have all of the above.
The DS controls are decent for GB games, but not as comfortable as the SP. The larger D-pad can sometimes be problematic (it hurts Super Monkey Ball Jr. particularly). It's also heavier than the GB systems and can put a dangerous torque on your wrists if you don't support them right -- hello carpal tunnel syndrome!
The GBA is more comfortable to hold and use for long periods, but without a backlight or a good bright houselamp, it's kinda useless. Also, the D-pad sometimes feels a little spongy. Oh, and if you get a backlight, your AA battery budget had better be big.
The Gamecube controller (for the GBplayer) sucks unless the game works well with the analog stick. The Hori controller's great, and the GBA-link cable combo is pretty decent, but it's not as satisfying a solution as the Super Game Boy was.
The SP has its drawbacks, but it's hard to think what they are. It's a beautiful system, you can find it at great prices, and if you get a standard model you can get some darn good packins. Buy one today
My gameplay distribution for GBA/GB games is like so:
GBASP: 65%
DS: 25%
GBP: 5%
GBA: 5%