A very clever solution but I think I must be missing something.

Why can't all the logic associated with sucking the data in, creating new tables if necessary and appending the new records to the relevant table all be encompassed in one T-SQL block instead of many SSIS flows / tasks?

Most of the required code would be virtually as Dave Lumley has already written it. That could then be called by SQL Agent? It could become a stored procedure but the performance gain would presumably be small if the quantity of INSERTs is high relative to the "think time" associated with coming up with an execution plan. The solution already requires the use of dynamic SQL and I can't see that can be avoided.