How important is motivation in determining the virtue/non-virtue of a karmic action?

I know I have acted in ways that were bourne of nothing but good intent, yet caused great suffering in those close to me.

I do not feel guilty about that, but I do feel sad. Intent is crucial, but ignorance can live underneath, leading us to act in ways that seem reasonable, but actually cause harm.

In the end, what is it you want? Do you want to look after your own karmic legacy, or do you want to reduce the suffering of those around you?

If the latter, then intent is very important, but the actual consequences matter too.

There was a case where the Buddha taught an insight practice to some monks. He came back a few days later and they had committed suicide. He undoubtedly learned from this! So, in terms of consequences, none of us are immune from 'unexpected' ones. Yet we can continue to learn, and find ways to increase the effectiveness of our actions in bringing about increased wellbeing in those around us.

Intention is indeed the most important element of karma.

From Dhammapada 1:

On one occasion, Thera Cakkhupala came to pay homage to the Buddha at the Jetavana monastery. One night, while pacing up and down in meditation, the thera accidentally stepped on some insects. In the morning, some bhikkhus visiting the thera found the dead insects. They thought ill of the thera and reported the matter to the Buddha. The Buddha asked them whether they had seen the thera killing the insects. When they answered in the negative, the Buddha said, "Just as you had not seen him killing, so also he had not seen those living insects. Besides, as the thera had already attained arahatship he could have no intention of killing and so was quite innocent." On being asked why Cakkhupala was blind although he was an arahat, the Buddha told the following story:

Cakkhupala was a physician in one of his past existences. Once, he had deliberately made a woman patient blind. That woman had promised him to become his slave, together with her children, if her eyes were completely cured. Fearing that she and her children would have to become slaves, she lied to the physician. She told him that her eyes were getting worse when, in fact, they were perfectly cured. The physician knew she was deceiving him, so in revenge, he gave her another ointment, which made her totally blind. As a result of this evil deed the physician lost his eyesight many times in his later existences.

Then the Buddha spoke in verse as follows:
Verse 1: All mental phenomena have mind as their forerunner; they have mind as their chief; they are mind-made. If one speaks or acts with an evil mind, 'dukkha' follows him just as the wheel follows the hoofprint of the ox that draws the cart.

Also showing the importance of intention is Dhammapada 124:

At Rajagaha there was once a rich man's daughter who had attained Sotapatti Fruition as a young girl. One day, Kukkutamitta, a hunter, came into town in a cart to sell venison. Seeing Kukkutamitta the hunter, the rich young lady fell in love with him immediately; she followed him, married him and lived with him in a small village. As a result of that marriage, seven sons were born to them and in course of time, all the sons got married. One day, the Buddha surveyed the world early in the morning with his supernormal power and found that the hunter, his seven sons and their wives were due for attainment of Sotapatti Fruition. So, the Buddha went to the place where the hunter had set his trap in the forest. He put his footprint close to the trap and seated himself under the shade of a bush, not far from the trap.

When the hunter came, he saw no animal in the trap; he saw the footprint and surmised that someone must have come before him and let cut the animal. So, when he saw the Buddha under the shade of the bush, he took him for the man who had freed the animal from his trap and flew into a rage. He took out his bow and arrow to shoot at the Buddha, but as he drew his bow, he became immobilized and remained fixed in that position like a statue. His sons followed and found their father; they also saw the Buddha at some distance and thought he must be the enemy of their father. All of them took out their bows and arrows to shoot at the Buddha, but they also became immobilized and remained fixed in their respective postures. When the hunter and his sons failed to return, the hunter's wife followed them into the forest, with her seven daughters-in-law. Seeing her husband and all her sons with their arrows aimed at the Buddha, she raised both her hands and shout: "Do not kill my father."

When her husband heard her words, he thought, "This must be my father-in-law", and her sons thought, "This must be our grandfather"; and thoughts of loving-kindness came into them. Then the lady said to them, ''Put away your bows and arrows and pay obeisance to my father". The Buddha realized that, by this time, the minds of the hunter and his son; had softened and so he willed that they should be able to move and to put away their bows and arrows. After putting away their bows and arrows, they paid obeisance to the Buddha and the Buddha expounded the Dhamma to them. In the end, the hunter, his seven sons and seven daughters-in-law, all fifteen of them, attained Sotapatti Fruition.

Then the Buddha returned to the monastery and told Thera Ananda and other bhikkhus about the hunter Kukkutamitta and his family attaining Sotapatti Fruition in the early part of the morning. The bhikkhus then asked the Buddha, "Venerable Sir, is the wife of the hunter who is a sotapanna, also not guilty of taking life, if she has been getting things like nets, bows and arrows for her husband when he goes out hunting?" To this question the Buddha answered, "Bhikkhus, the sotapannas do not kill, they do not wish others to get killed. The wife of the hunter was only obeying her husband in getting things for him. Just as the hand that has no wound is not affected by poison, so also, because she has no intention to do evil she is not doing any evil."

Then the Buddha spoke in verse as follows:
Verse 124: If there is no wound on the hand, one may handle poison; poison does not affect one who has no wound; there can be no evil for one who has no evil intention.