I've been looking into this lately too.
Seems like the bottom line is that every credit card you apply for is gonna take 5-10 points off your credit score in the short term, but that effect will wear off over the ensuing year. To get the best rewards, you need to have excellent credit to start with, and if you do it right and space everything properly you should be able to maintain excellent credit.
I did a moderate amount of research and decided for my situation it's probably worth it, but haven't been able to pull the trigger on much. Mostly I want to avoid anything that will change my spending patterns by making me want to hit minimum spending to get rewards. Just a dangerous path to go down, I already have to work pretty hard to fight lifestyle inflation.
To dabble, I got the 2 best sign-on bonus cards I could find. I should be able to hit the spending minimums and spend the rewards without changing anything I do. $950 in (effectively) cash without doing anything and my credit score doesn't seem to have budged. Once I hit the minimum spend on those I may go for another round and see what I else I can do.
They say that you should leave the cards open a while. Apparently there's some risk of a CC carrier "blacklisting" you for getting the bonus and immediately canceling. I'm planning on cancelling mine after 10-11 months, right before the year 2 annual fee comes due.