We have a number of daily processes with various 3rd party organisations that rely on the transfer of data. At the moment this is done through the dropping and retrieving of csv/xls files from various SFTP locations. I want to replace these processes with database connections to locked down users/schemas. At worse, I want to ensure that any new development with 3rd parties are done in this way and there are no new processes that are reliant on file transfers.
Now in my mind, this transistion makes sense but I don't really know if this is the best practice or what the pros and cons of this will be, especially given that the existing processes do work (most of the time).
Does anyone have any thoughts or advice that they could share?