So this is a very broad question, and you need to take anything I say with a grain of salt. There are innumerable factors which could invalidate the advice I'm giving you here.
That said, an approach I have used repeatedly and to great effect is to create raw (i.e. unindexed) tables that match the schema of the incoming files, then using some means of loading them from the files. The fastest ways to load data into SQL is going to be through SSIS, BCP or C# SqlBulkCopy
Once the data is loaded into your raw tables, have "workoff" procedures whose job it is to merge the raw data into your presentation tables. You want to use stored procedures because you want to avoid doing row-by-row modifications (which if you're processing updates, SSIS, or C# would likely impose upon you).
You'll also need to take downtime into consideration. Any time you're updating the tables people are querying, you will run into blocking, as SQL ensures someone can't read a row which is currently being modified (i.e. by an Update).
Try to minimize the time you are actually updating the live table; make use of temporary tables for any pre-processing you need to do, and try to only do a single set of insert/updates at the very end.
There are ways to prevent readers from being blocked such as using nolock, but that comes with it's own set of caveats. Basically, you can prevent readers from being blocked, at the risk of them reading bad data; probably not something you want in a payroll system.
If you find your blocking is getting out of hand, consider batching your insert/update/deletes so that the locks you take out are very short in duration. This will increase the time it takes to get the data merged into your presentation tables, but will keep end users happier if blocking is a problem.
The part I can give you the least advice on is how to know when to kick off your process. Your options are poll, or be notified. Polling involves constantly checking a file location to see if you have new files to process. If you don't, silently exit, and run the process again as quickly as feasible. If you do, process the files, then move them somewhere they won't be picked up again. Alternatively, if you have some means of listening for events saying a new file was delivered, you can act on that and kick off your process on demand.
I'd also recommend keeping track of what files you processed, and if possible, how many rows you processed. It's one of those things which is very useful for debugging when things go wrong, or if you have to re-process files, or figure out which files you never processed.
So to sum up:
- Try to act on new files as quickly as possible. Events allow you to act quickly and only when needed, but can add complexity. Polling is simple, but introduces a small delay, and most of your runs will end in a soft failure
- Load data into un-indexed raw tables as quickly as you can. Aim for something that loads data in bulk like SSIS, BCP, or SqlBulkCopy over systems which have to loop over every row in the source data and execute a separate insert statement
- Once your data is staged, use stored procedures to merge the data in where you want it
- Think about logging what files you run to save future-you some hair loss
Good luck to you!
Edit Adding some context to your follow up question
Again, please take this with a grain of salt, because this is fairly generic advice. Whether you create one database or more is going to depend on your particulars; depending on if you only need a single unified live customer database, or if you need to do distinct things with the live data for each customer. At my company, we have a bunch of different clients who send us healthcare data (where we follow a similar model as described above). We need to ingest all the differently shaped files, then merge them into a single unified schema.
We use a single database, and a distinct schema for every set of [uniquely shaped] files. We load the raw tables, then have a procedure for each one which loads the cleaned up data into our live core schema. So basically one unified live database, and several differently shaped schemas on the same database.
In another corner of our system though, we do it slightly differently. We have a dedicated database for each set of files in which we have raw tables, and a set of live tables of basically the same shape. The reason we do these differently is that we have the need to query those datasets independently of the unified set of tables mentioned above. We still wind up merging those distinct databases into the unified core schema, we just do that off of the live, customer-specific databases, rather than working right off the raw tables in the same database.
So in short, I think whether you have different databases or not is probably going to depend on whether you need to have multiple live versions of each distinct customer's data, or if your end product is really just the unified schema.
