If you know the history of the car you can decide if it is a good investment. Or put another way-if the car is obviously modified and beaten on stay away from it. That is unless you have about $3500 to spend on a tranny rebuild from Honda or Howard.
I take our 99 SS to Pocono and The Glen and run it very hard. NO problems with the tranny at all in either normal or SS mode. The important thing is to flush and fill the gearbox according to the service intervals that Honda gives you. A drain and fill should be done every 15K and a full flush at 45K for normal driving. Do it more often if the car sees hard usage.
I do not drag or hard launch the car. In fact, the SS tranny is not really designed to do that from the factory. The computers do control it to a degree. Once again, the SS is a great gearbox if properly used. Beat on it and it will break. Some people got the early version and those did fail when pushed really hard.
For those that say do the swap-keep this in mind--you will lower the resale value of the car. If you intend to keep it forever (10 years or more) then by all means consider the swap. I'm keeping my car as is-it is a personal and financial decision I can live with.

Good luck and welcome.