SSIS – SSIS Server Permission Error

On the development environment, we have different user accounts to develop the SSIS packages and every user account is not authorised to access the file from the shared location. If you have changed the file storage location and processing user account does not have the access right on the new storage location then permission error will arise through the job.

Error Condition:

If we run the package from BIDS/SSDT then it works fine as well as if I run the package inside Management Studio (Integration Services Catalogs) then it works fine. But It does not run when we schedule the job through SQL Server Agent and show failure error code 0xC020200E as given below:

Description: component "Flat File Destination" (194) failed the pre-execute phase and returned error code 0xC020200E. End Error DTExec: The package execution returned DTSER_FAILURE (1).

Many a times even granting full control to the user account under which the package runs, does not resolve the error. So what you will have to do is add the user under whose account the package runs to the destination servers "Power Users group". This will give some permission to the user account and resolve the security issues.

In our last released, we have figured out that if we attempt to run the Package from Integration Service Catalogs then packages were running successfully but after going into the job schedule via SQL Server Agent then packages got failed due to access denied issues as given below:

To resolve this issue, you must need to set the access permission on the file storage directory or location. For example, add the User account that runs SQL Server to the folder with read/write permissions.

To share folders with other users on your network in Window 10

Right click the folder you want to share and click on advanced sharing as shown below-

In the Properties dialog box, select Everyone and set permission Read/Write. After doing this, click on the Share button as shown below:

Doing this gives everyone who is a user full access to the data in the file.

Conclusion

It's much saver to use proxies in the SQL Agent job and give that proxy account specific permissions on that file/directory. Even adding the proxy account to the Power User group might be insecure, as you're giving that account potentially too much permissions.