You can do this in Access too... Open a front end (no data, just linked tables, and some code). Then loop through the table(s) and deal with them. Append to linked table etc.  I think when you have tables without standard naming conventions, it's easier in Access (maybe because I'm terrible at SSIS).  But you can loop over a table's Fields collection, and it's pretty trivial.  Also, if you have information stored in a table's name, you can use TableDefs(n).Name to stuff that into a variable and chunking it and figuring out what to do with it from there.

Just another option. Sorry it's not a direct answer to your question... I just find fixing screwy designs easier using DAO than using SSIS.