I have created a SSIS package.
If I run it from Visual Studio, it works.
If I deploy it to the server and RDP to that server and run it from SSMS, it works.
If I add it to a job with a Proxy Account and run it from the job, it works.
It does not work and fail when I run it from SSMS that is on local desktop. Any idea why the local SSMS cannot run it while the SSMS on the server can run it?
Please post error. Which account is SQL service or integration service running as on desktop?
I continued to narrow this one down by removing the export to files. As soon as I did that, the package ran fine which means that it is not the security at the package level but at the file/folder output level. If I RDP into the server and use the admin account to run the package from SSMS, it works fine.
It fails when I run the package with the same admin account and from SSMS when run local with a different window login. Therefore, have to find out what account is being utilize although I launch SSMS as admin but it must be running the package with another account that does not have access to the folder/file.
either the sql server service or the integration service account running them. which usually default to some machine level account.