The story is told in response to the question of whether or not one can atone for a parent’s sins:
Rabbi Akiva once saw (what he thought was) a man struggling with a heavy burden on his shoulders and bemoaning his lot in (what Rabbi Akiva thought was) life. Concerned that this might be an overworked slave deserving to be freed, Rabbi Akiva asked the man what his story was. The oppressed laborer replied that he was the soul of a person who committed every conceivable sin and that if he stopped to talk, he’d get in even more trouble.
The punishment of this particular sinner was to gather wood, which was used to burn him every day. Rabbi Akiva asked if there was any way to free this soul and the deceased replied that the only way was if he had a son who would stand in front of the congregation and say “Barchu es Hashem hamevorah” or “Yisgadal v’yiskadash…,” after which the congregation would reply, “Baruch Hashem hamevorah l’olam voed” or “Yehei shmei rabbah…,” respectively. (These are the prayers of Barchu and Kaddish, in which the leader of the service calls upon the congregation to praise God, which they then do.)
Finally, Rabbi Akiva asked the man who had survived him; the spirit replied that his wife had been pregnant when he died. Rabbi Akiva recorded the name of the deceased, the man’s wife, and his hometown so that he might investigate the matter.
Hurrying to the man’s city, Rabbi Akiva discovered that the deceased was particularly reviled by the townspeople. He had been a corrupt tax collector who took bribes from the rich and oppressed the poor. Among his more notorious deeds, the man had violated a betrothed girl on Yom Kippur! Rabbi Akiva located the widow, who had given birth to a son. So despised was her husband that no one had even circumcised the child. Rabbi Akiva took care of this grievous omission and, when the child was old enough, he taught him Torah and how to daven in shul, including the prayers the man had specified.
As soon as the boy recited the appropriate prayers, his father’s soul was relieved of its harsh punishments. The man’s spirit re-appeared to Rabbi Akiva in a dream to thank the scholar for saving him from the tortures of Gehinnom.
It would appear that this story does not represent the origin of these prayers (which presumably would have been composed by the Men of the Great Assembly), but merely the practice to recite Kaddish as a merit for deceased relatives. Other versions of the tale may be found in Midrash Tanchuma, Zohar Chadash, Beis Yosef Yoreh Deah 376, Machzor Vitry, and many other places. Some sources say that the prayer in question was Kaddish, while others only mention Barchu. An even more striking variation is that the version in Tanna d’Bei Eliyahu Zuta attributes the story to Rabbi Yochanan ben Zakkai rather than to Rabbi Akiva. Nevertheless, the big picture is always the same: having a child or other relative as a catalyst for the congregation to praise God is an indisputable source of merit for the deceased.