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.
Thank you. I appreciate your answer but I am not an Access developer. I have been asked to use SSIS. Cheers.
If the fields in the Access tables don't match, how are you going to map them? I had to do this a long time ago... pretty serious hassle. You could create a "dictionary" of sorts of the field names in the source databases, and then write the unique values to a table and then add another column so you get (SourceColumnName, DestinationColumnName) pairs. Then you'd connect to a new Access "backend" database, loop of the the fields in the table you need, and create the insert statement from the value pairs, and then execute the dynamic SQL. Then go to the next database. You can loop over the directory using DIR etc. (Or use a FileSystemObject).
Thanks but I probably didn't explain myself well enough. I have the same tables in each of the Access database files but there are many of these same Access files in backlog and coming in new. I want to loop through them in the folder then import each. I am getting the feeling that no one does this with SSIS? That surprises me a lot because I thought that would be a common situation. I want to use Access as the file source only but not do development with it. Maybe I can try asking, say, PragmaticWorks (Task Factory SSIS extensions) or CozyROC, if they have something for this or can point me to how to get started doing it. or, maybe they'll say, "not possible without coding in VBA".
I really do appreciate all your responses and I'm sorry I can't do a VBA/DOS batch solution.