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.