I think makes perfect sense for archiving. I keep hearing tapes last 30 years, while in the same room temperature SSDs last a few years. Just wish I could find a solid source for it!
You've probably been reading bad sources. There is a very real relationship between the number of P/E cycles that a piece of flash memory has been through and the unpowered data retention it can provide. Lots of publications and even more random forum posts repeat statements about SSD data retention without including the caveat that the numbers apply specifically to worn-out drives. Brand new flash memory can easily have unpowered data retention in excess of a decade.
If you're reading a source that claims SSD unpowered data retention is only a year, and that source does not qualify that with a statement about the prior condition of the drive, you should assume that you're reading another misinterpretation of the JEDEC standards.
The drives and the software are the problem.
Drives are very expensive and there isn’t much non enterprise software to manage tape backups.
Enterprise software is expensive and you won’t likely be even able to buy it and since cloud storage and NAS became commoditized all the backup software that targeted small businesses pretty much either died off or went cloud backup only.