But what was there in that vision that would have served to reignite his spirit? It was a story of failure and frustration that ended in despair. I know that the episode ended with Sisko's spirits raised, but I don't see why that particular story would've been chosen as the way to raise them, when it ended so hopelessly. I've always found that a flaw in the episode. It's a cool format-breaking story about the 1950s and all, but in-universe it was always difficult to see the cause and effect. The fact that the second Benny appearance was explicitly a Pah-wraith trick made the first one make sense to me for the first time, once I considered that maybe they'd both been attempts to break Sisko's spirit and that they'd both failed/backfired.
Plus it makes the Prophets real jerks, ruining that poor guy's life just so they could send Sisko a cryptic vision they could just as easily have fabricated as a pure illusion.
For me the point is that at the beginning Sisko is feeling as if everything he does is futile. This is a person who is tasked with an enormous responsibility and the Prophets take him and show him what it is like to live a life of true futility, where he doesn't struggle merely against a foe for the survival of his way of life, but struggles to be accorded basic human respect.
Compared to the trials of being told that because of the color of your skin you're not good enough to sit at the same lunch counter as people of a different color skin, the travails of the Dominion War while no less trying I think are put in perspective for Sisko at least a little bit.
I also don't think that The Prophets did anything to "ruin" Benny's life. I think that what happened to him would have happened any way. I think that the basic idea for his stories came to him all on their own, and it was just the details that he got from his connection with Sisko.