Comparing my $1600 Sony SCD-777ES with a similarly-priced SOTA vinyl rig and using the same program material, SACD sounds, to my ears, more "there." More expensive vinyl rigs may be better.
Speculating about the future of SACD is in one sense a crap shoot, but, for what it's worth, Classe and Krell have both indicated that they will produce (very pricey) SACD players...suggesting they see a future, at least in the niche market. At the same time, Sony indicates it will release even cheaper players (down to $299 list) at some point...so that suggests something more than "niche."
In addition to Sony, Telarc, Audioquest, Groove Note, Hyperion, Vanguard Classics, DMP, and Water Lily Records, EMI, Virgin, and BIS have released or will release some SACD's (Virgin's "Tubular Wells" is already on the market), while some other record companies "wait and see."