Your proposed project sounds fairly decent. However, the more hands involved the greater complexity security is.
If this was my request, I would ask some of these questions:
Is the data stored currently on multiple data centers or centrally?
Are these sources in the same domain? Or do we need multiple SSL’s for our ETL?
If we choose to rely on external sources to transport this data to a local/network drive, how secure and consistent is this system?
Can the system of question 3 be integrated into SQL Server or another software to copy or pull the data?
Why Network drives are best
For one, durability of the directories is independent of a SAN, disk, or single blade, so the data remains available even during outages.
Because they are on a single mount point, where each drive is additionally mounted, your security is simplified dramatically.
You could give the files to SQL Server by using a single account that only SSIS has privy to, or a Domain Account that your AD manages.
But really, you should have an ETL process developed
- You ensure the validity and security of your documents as you go from SOURCE to TARGET.
- the process is encapsulated away so that additional sources is a mere plug ‘n play.
Proper ETL involves 3 stages:
- Extraction (your primary concern)
- this involved programmatic pulling and staging of your source data.
- Unless it is absolutely impossible, this process should use as little manual effort as possible.
- If you find yourself with a lot of manual effort in this stage, often the data source may not be clean enough for your purposes.
Transformation (inside the database/SSIS package)
- This involves all processes that clean and ready the data for loading onto the final target tables.
- cleanup usually follows this: Removing immediately bad data rows. Separating multifaceted values into their own column. Performing transformation steps that changes these values to a pre-final step. Last combinations of said columns that create the final tables OR allow your final loading to be collections of these values in a organized fashion (such as collecting from each sporting Team website details of their teams/history and then aggregating this in a final table)
Loading. The final, cleaned, and final form for your data.