the sega CD was NOT a flop. it had a three year plus lifespan, over 100 titles, and multiple hardware variations. it was the most successful console addon ever released. it wasnt a runaway success like the genesis, but it wasnt what brought sega down as a company. in fact its mild success gave sega enough confidence to think an addon would work, and they went ahead with the 32x, which was a failure
Console add-ons, as far as I can tell, has never been succesful in America. In Japan, I think Nec did very well with the TurboGrafx16/PC-Engine CD and it's various system cards, but I'm not a 100% sure about that. If you read game coverage from that era, you will see that even then the SegaCD was very criticized for not just it's price (much more expensive than a regular Genesis console) but also it's game library, wich consists mostly of FMV games and enhanced Genesis ports. As a Sega fan that just recently got one, I find myself disapointed with most of what I have played so far, even though I did not pay full price for either the console or the games.
I don't think the reason behind the 32x was the relative "success" SegaCD. Sega of Japan was planing to launch a "new" console, basically a Genesis with more colors. The current president of Sega America thought that if all it was going to do was add more colors, then Sega should turn it into an add-on. Evantually it became a 32 bit add-on that, once again, costed more than the Genesis itself, with a chip setup slightly similar to the Saturn.
By the time Sega released the Dreamcast, they had commited too many mistakes in the past. They no longer had the credibilty and the amount of cash necessary to subsidize a console until it became profitable, and could not fight the powerful Sony PR machine.