Although I am not totally clear on the end goal here, would an execute SQL task embedded in the OnError event handler of either the data flow task (local to the DTF) or at the package level (global to the package and having any package errors bubble up to that event handler) do the trick for you?

This would be very similar to what Microsoft published in Project REAL.