Until now I have used an on premise SQL Server 2012 and performed manual ETL operations to create/manage a data warehouse that contains data from multiple database application from different organizations.
I get the source data [several dozen csv/txt files some of them 50 MB in size] every month using 2 methods: 1) execute sql queries on the source databases (for the client-server applications) and 2) using a web-based reporting tool (for the web-based applications). The destination are databases in my SQL Server based data warehouse, which I manage using SSMS/SSIS. This manual process easily takes several hours each month to update the data warehouse. I now want to update the data on a more frequent basis (possibly daily) for better analytics/reporting. I suppose it would involve gaining direct access to the source database applications?
I need to get some high level information on the methods/tools out there to accomplish this and make this process efficient/automated.
