While similar questions have been asked, I don't believe kinetic bombardment was brought up, with focus centred around nuclear warheads.
Considering the ability for a "rod from god" to embed into the earth and cause immense destruction to the surrounding area, would such a scenario be realistically capable of triggering Yellowstone?
Short answer: nope.
TL;DR: unless your kinetic penetrator resembles the Chixulub impactor it will be too small to do the job.
Longer answer: a kinetic penetrator cannot penetrate very far into an object. A "rod from god" was expected to have a terminal speed of "mach 10", which at sea-level is about 3.5km/s, which is high but not super high, so lets use the Newtonian approximation for impact depth: $D \approx l {\rho_p \over \rho_t}$ (where $D$ is the impact depth, $l$ is the length of the penetrator and $\rho_p$ and $\rho_t$ are the densities of the penetrator and the target respectively). The rod, therefore, is unlikely to penetrate more than about 7 times its own length in volcanic rock, and with a length of only 6m or so that's less than 50m. So much for penetration.
But what about power? I can't find the exact figures of an RFG right now, but as they weigh about ~9 US tons and hit at ~3.5km/s they release about 50GJ, or ~12 tonnes TNT equivalent. That isn't a particularly substantial boom, and compared to the multi-kilometre-deep layer of rock above the Yellowstone magma chamber is isn't so much superficial as practically unnoticeable. You'd have to hit it over and over and over again, and with each successive impact some of the debris will fall back into the crater so you'd have to pour energy into widening the crater and throwing debris out and away instead of up.
Etc etc ad nauseam. Your assumption that these weapons "embed into the earth and cause immense destruction to the surrounding area" is basically a bit off... the rods from god are practically precision weapons, compared to a nuke. Great for cracking a shallow bunker, rubbish at penetrating 10km of volcanic rock.
ETA: what if you used a bigger RFG?
You might consider looking at the Earth Impact Effects Program which simulates asteroid impacts on Earth. A kilometre-wide chunk of iron hitting the ground at ~11km/s will be enough to (temporarily) blast away about half the thickness of rock over the caldera. Between that and the compressive effects of the impact, you might reasonably expect to get an eruption.
Of course, such an impact already delivers gigatonnes-equivalent of power and utterly lays waste to everything for hundreds of kilometres around it and would kick vast amounts of crud into the atmosphere that would have major climatic effects. If you did managed to kick off a true VEI 8 eruption you might release ten times as much energy again, perhaps making the effort worthwhile, but it isn't clear that there's enough pressure underneath Yellowstone to deliver that much oomph so you may as well just content yourself with asteroid bombardment instead.
I'm going to expand a little bit on StarfishPrime's otherwise excellent answer.
First of all, the whole point of the RFG is NOT to cause 'immense destruction to the surrounding area'. The advantage of a kinetic impactor compared to a nuclear weapon is that the damage is extremely focused, so a kinetic bombardment of the kind you're thinking would actually be less effective for the purpose of triggering a volcano than a nuke would.
Second, although a kinetic impact COULD do the job, it would require a colossal impact. The magma chamber under Yellowstone has 8km of solid rock on top of it that you need to address. For comparison, here's a list of all the asteroid impacts in Earth's history that could have created a crater deep enough. Note that you wouldn't actually need an 8km deep crater for this, 4 or 5km would probably do the job because at that point enough overburdern pressure on the magma chamber would be released that the gasses in the magma would start coming out of solution and blow the rest of the rock out of the way.
It's worth noting that at least half of the 40-odd impacts on that list were large enough that the additional destruction caused by breaching the Yellowstone magma chamber wouldn't really matter that much.
Third, to do the job with a nuclear weapon you'd want to drill a hole at least 3 km deep and put a REALLY big nuke down there. Again operating from the assumption that you'd need to get rid of at least half of the 8km of rock to let the volcano do the rest of the job, you'd need a cracked zone of 2km radius which (if I'm doing my math right) would require a 300 megaton bomb. For comparison, the largest nuke anybody has ever built was only 50 megatons.
Many events - such as earthquakes - are known to trigger volcanoes, however this is uncommon in North America for a variety of reasons beyond my pay grade.
That said, a "rod from god" is an event which could certainly trigger Yellowstone if (a) it were strong enough, and (b) were placed in exactly the right spot.
However, triggering a volcano does not mean making the volcanic eruption worse. The pressure buildup at Yellowstone suggests that the next eruption will be relatively minor - some lava flow and minor amounts of ash. So even if this "rod from god" triggered Yellowstone, it likely wouldn't cause all that much damage. The damage caused from the kinetic bombardment would be significantly worse than the fallout from Yellowstone.
