n Access DB I'm looking for some guidance on how to use SSIS to process Access database files into SQL Server. I know how to process flat files thanks to a lot of good tutorials I've found but when it comes to Access DB files I've only seen cases where all the tables in the Access DBs are of the same structure. I have a directory of Access DB files that I want to import to SQL Server then move to an archive directory. Should I use For Each Loop Containers with a separate one for each table type in the Access DB? In other words, if I have an Access file it would be opened again each time a new table within it is imported? It seems like there should be a way to open the Access DB once then process the different tables but I don't know how. There can be any number of Access DBs with different dates in the their names so I would use the foreach loop to process as many as there are in the directory, moving each one to archive after all tables are imported. Thanks in advance.
I've read all the other answers so far, but to be quite honest, SSIS just is NOT the right tool for the job. Microsoft created SSMA, or SQL Server Migration Assistant, to do this kind of work. Just be sure to get the right version for the combination of Access DB version and SQL Server version. It's relatively simple to use, but does require some thought and experimentation the first few times, so be prepared to drop the tables it creates and start over. You will likely have to do some massaging of it's default setup for a given table with regard to data types, but in the long run, it's the only really decent tool for the job. Using SSIS for such a task is probably not worth the trouble.
Thank you but the incoming files will always be Access files so I need a way to process them via an automated, unmanned job. This is one of many projects that will have files coming from external entities that need to be imported. I can do Excel and flat files but I need advice about how to use SSIS to import Access DB tables..