I have an SSIS package that I would like to run as a job. The flat file source sits on Server A while the package and job sit on Server B.
When I run the job that executes the SSIS package I get an error (Error code 0xC020200E Cannot open the datafile) but I can manually execute the deployed package in SSMS and it executes successfully . I had found out that this was caused by the Server B's NT SERVICE\MSSQLSERVER service account not having access to the folder on Server A. To test this I had created a test folder on Server B and made the SSIS package look there, I have then given NT SERVICE\MSSQLSERVER full access to the folder on Server B. The job executed successfully and the package ran however when I try to give NT SERVICE\MSSQLSERVER permissions to the folder on server A, I can not find the server in the locations tab and I cannot access the NT SERVICE\MSSQLSERVER service account. I have tried mapping the network drive however that did not help. The files are automatically uploaded onto server A every morning, the folder has to stay on Server B. Any assistance or advise is appreciated.