When you develop an SSIS package and run it in the debug mode locally in the visual studio, normally everything works fine. However when you deploy it to SQL Server, you face folder level permission issues.

In order to solve that you need to give permissions to folders that are accessed from the SSIS Package.

There are 2 permissions that I give for this kind of situation.

1. Default instance of the Database Engine service:NT SERVICE\MSSQLSERVER
2. The default instance of SQL Server: NT SERVICE\SQLSERVERAGENT

You can find detailed information from the link below

https://docs.microsoft.com/en-us/sql/database-engine/configure-windows/configure-windows-service-accounts-and-permissions?view=sql-server-ver15

For example, you are logging something to a file, in that case you need to right click to the folder, properties, security and edit. After that you can give permissions to folder. For SQL server, I usually give full permission in order not to face any failures.

Now everything should be working fine but recently I was having a problem with a process task called WINSCP.

It is a tool for accessing SFTP servers to do operations like downloading a file.

You can watch how to configure WINSCP: https://www.youtube.com/watch?v=PLHoYhwG2iI

I configured everything to download a file a process and it is working in the debug mode perfectly. Then I deployed the package to SQL Server and it did not work. I have given all the folder permission that I have talked about yet it failed terribly. Normally the package need to run everyday as a scheduled job since I could not able to run it on the server, I had to run it manually everyday.

Then I found the solution.

https://winscp.net/forum/viewtopic.php?t=12277

As explained in the link, I have downloaded the process monitor procmon which is a microsoft product in order to examine the processes in detail. Because I have checked all the logs that SSIS Package produce and there were no useful information.

After running the procmon, I set a filter like that after I understand there is a access denied issue.

Then I found the folder that WINSCP tries to open and gave the same permissions and the issue is successfully resolved.

It uses this folder to log some information and it is almost imposible to notice this folder without procmon.

I hope that guides you if you have a similar problem in the future.