There are a few problems with this idea.

Firstly, and this is a nuance that won't have been taught at a school level, the missionaries weren't Catholic. This is partly because what we would today call 'Catholicism' had not been developed, and the formation of the Christian group that would become known as Catholic was ongoing as the Western Roman Empire collapsed. It would be more accurate to call it Nicene (after the Nicene Creed) rather than Catholic, though it is also referred to as the 'Latin' church. However, the biggest problem with the idea is that the missionaries who converted the Germanic tribes that would later invade were Arian, not Catholic. Specifically, the man spearheading the effort was Ulfilas, a Gothic Arian who brought the religion to multiple Germanic tribes. The differences between Nicene Christianity and Arian Christianity were rather slim - the core difference was that Arians believed that Christ was begotten by God, and therefore inferior in the hierarchy of the Holy Trinity. The rest of the Christian factions rejected this. That sounds like pointless esoterica to us, but at the time it was super important.

The kingdoms that formed following the fall of the WRE were almost all Arian. The Visigoths, Ostrogoths, Langobards, Burgundians, and Vandals were all Arian at the time they conquered Roman territory.

But Ulfilas did not tell stories of cities paved in gold, and was not responsible for the invasions/rebellions of Germanic tribes that destroyed the empire. The circumstances of their attacks had very little, if anything, to do with religion. The Visigoths, for example, began as refugees seeking protection from the Huns. They settled in the Balkans and along the Po Valley, were treated rather poorly by the Romans so they rebelled and - after a lot of difficulties - settled in southern France from which they would conquer most of Iberia. Their attacks were about politics and ethnic tension, of which religion was a part but the main gripe the Visigoths had was being unrewarded for good service to the empire when their Roman colleagues got so much more for the same work (or at least, that's the gripe the leadership had, which is all the sources really care about). The Ostrogoths conquered Italy at the behest of the Eastern Roman Empire. They fought not because of loot they might get from Rome, but because of the promises of wealth and power offered by the eastern emperor. The Franks weren't even Christian when they invaded.

The idea also ignores what was probably the dominant cause of tribal movement - the Huns. This situation, argued by Peter Heather and often called the 'billiard ball' explanation, goes as follows. The Huns sweep in from the east creating havoc. We know from the late Roman historian Ammianus Marcellinus that they were terrifying and obliterated the Goths, killing two of their rulers in a single battle. So the Goths move west (or south to the borders of the Roman Empire, in the case of the groups that would become the Visigoths). This displaced the people who were already there, who also move west. Which displaced the people who lived there, so then they also move west. You get the idea. The Germanic tribes knock each other about like billiard balls until some of them have no choice but to march into the Roman Empire when the relentless advance of the Huns keeps pushing everyone west. This can be seen in the actions of some of these tribes. The Vandals, for example, didn't start conquering a new kingdom until they hit North Africa. They ran across an entire continent! The Visigoths did something similar. Although they had sought refuge under the Roman Empire, their first course of action when it became clear after the Sack of Rome that Visigoth-Rome relations were unrepairable was to try and sail south to North Africa. When they couldn't find enough boats, they headed west over land. These Germanic peoples that had been converted to Arianism did all in their power to get as far away from the Huns as they could, and when they faced a choice between fighting the Huns - a seemingly unstoppable and terrifying force - and risking it against the Romans - a familiar opponent - the correct choice becomes obvious. What Peter Heather argues is that, in the knowledge that the Huns were coming, the Germanic tribes coordinated to invade the empire because it was the least worst option. It was only when the Huns reached France, when there was literally no more west to flee to, that they pooled their armies and fought the Huns at the Battle of the Catalaunian Plains in 451. The theory is not flawless, but the Huns certainly contributed a lot to the Germanic incursions into Roman territory.

Then we also have to look at internal problems that contributed to the collapse of the Roman Empire - poor administration, high corruption, social divisions, financial difficulties etc. You can read about some of those in the Decline of the Roman Empire section of the FAQ.

So the contribution of Catholic missionaries to the fall of the Roman Empire was negligible.