SSIS Flat File Folder Permission

Question

I am using SSIS to export a CSV file to a folder location that needs credentials for access so we can SFTP it to a client.

What permissions do I need to set? I don't see any way to set username and passwords in the Flat File Destination.

Currently I get a error when trying to export the file:

Warning: 0x80070005 at Data Flow Task, Flat File Destination [2]: Access is denied.
Error: 0xC020200E at Data Flow Task, Flat File Destination [2]: Cannot open the datafile "\\SERVER\FOLDER\Dropoff\ELIGIBILITY.CSV".
Error: 0xC004701A at Data Flow Task, SSIS.Pipeline: Flat File Destination failed the pre-execute phase and returned error code 0xC020200E.

I thought I had generated a file in a folder with permissions before but I don't do a lot with SSIS and don't remember what I did.

Answer 1

SSIS simply executes as the user running the package. So if you are running from your dev machine it is your Windows credentials which will need access.

If you are running from a SQL Agent Job then the SQL Agent Service Account user will need permissions (or use a proxy)

Answer 2

Just in case this helps anyone, I was also getting this error and could not figure out why it would execute when running the package directly (in my case through VS) but not from SQL Server Agent. All the permissions seemed correct. I finally tried deleting the existing file (I had it set up to overwrite the data in the existing file in that directory) and re-ran the job and it worked. After that, it has worked fine. So there seems to have been some permission issue (maybe?) or conflict with the file I originally created through VS and the file created through the SQL Server Agent job.