Sql-server – How to load more than one MS-Access-db tables with data in SQL-Server-DB

You'd have to optimize the source and I'm not sure what optimizations are available for Access - a quick Google search reveals this and something about indexes here but I'm honestly clueless about how Access indexes work.

It occurs to me that you could create a linked server to the Access database and then just schedule a job to run a query against the link.

EXEC master.dbo.sp_addlinkedserver 
    @server = N'MyAccessSource', 
    @srvproduct=N'', 
    @provider=N'Microsoft.Jet.OLEDB.4.0', 
    @datasrc=N'C:\my\accessdb.mdb';

or even query the source using OPENROWSET

SELECT CustomerID, CompanyName
   FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
      'C:\my\accessdb.mdb';Table);
GO

or

select * from 
  openrowset('Microsoft.ACE.OLEDB.12.0', 'C:\my\accessdb.mdb',
  'select * from Table');
GO

I guess this would save you the overhead of firing up SSIS packages and worrying about data flow tasks.

If you want to store the configuration file and/or the file you need to "pick up" via the SSIS package on a UNC path, SQL Server is going to need permissions to that direct UNC path. This permission is based on your service account being used for SQL Server Agent. If you are in a domain environment, then it is best to have your service run as a domain account and it will allow you to easily provide access to network resources.

If this is a configuration that is meant to change often and I would not be the one to do it necessarily every time, then I might chose to store the configuration in SQL Server. There are a few examples out there online that show how to do this, just one example here.