I need my SSIS packages to pull flat files from a shared folder.
Users will dump these flat files to their respective folders within the shared folder on a per project basis. The package (folder watcher?) will see new files were added and execute the package to get the data in the database.
My question is: Does it matter where the shared folder is hosted? Is better to have this shared staging folder on the same server where the DB is hosted? That way the packages will deployed server side and always pointing to the sub folders where people are dumping data.
On a side note, some of my flat files are in a JSON format, which SSIS doesn't play nice with.
- Does it matter where the shared folder is hosted? Is better to have this shared staging folder on the same server where the DB is hosted?
It's always good to have shared folder on same server where SSIS job is running. Else you'll have to use UNC Path. UNC looks like ""\\server_name\D$\FOLDER\". It can be cumbersome if not used properly, with all permissions.
For JSON, I believe SQL 2016 has started supporting JSON.