SSIS Package run via a job step with a proxy. Which account determines where BufferTempStorage goes?

Question

And what write permissions are required.

I've asked both Bing AI and ChatGPT and they contradict each other. Is it the SQL Agent service account or the Proxy account that determines where the files are written and which account requires Write permissions on that directory?

Answer 1

When running an SQL Server Integration Services (SSIS) package via a job step with a proxy, the account that determines where BufferTempStorage (temporary storage for the buffers used in the data flow task) is placed is typically the SQL Server Agent service account, not the proxy account.

The SQL Server Agent service account needs to have the necessary permissions to be able to write to the directory specified in the BufferTempStoragePath property of the data flow task. The BufferTempStoragePath is used by the data flow task to swap data to disk when it cannot hold all the data in memory.

The proxy account is used for executing the job step itself, not for controlling where the BufferTempStorage is placed. It should have permissions for the tasks it is meant to perform.

If you're experiencing issues with this, please ensure:

The SQL Server Agent service account has proper write permissions to the specified directory.

The directory specified in the BufferTempStoragePath is valid and accessible.

Answer 2

Hi @Nick Ryan,

As said in Data Flow Performance Features.

Specify the locations for temporary storage of buffer data (BufferTempStoragePath property) and of columns that contain binary large object (BLOB) data (BLOBTempStoragePath property). By default, these properties contain the values of the TEMP and TMP environment variables. You might want to specify other folders to put the temporary files on a different or faster hard disk drive, or to spread them across multiple drives. You can specify multiple directories by delimiting the directory names with semicolons.

From my opinion, the proxy account needs to have the permission to the path.

Regards，

Zoe Hui

---

If the answer is helpful, please click "Accept Answer" and upvote it.

Answer 3

Zoe and Amira's answers seem to contradict each other which is the issue I've found in my earlier searching. Unless I've misunderstood what you are both saying. It seems very odd to me that this is not something documented clearly by Microsoft as surely it is of interest to anyone using SSIS.

My colleague

Answer 4

Zoe and Amira's answers seem to contradict each other which is the issue I've found in my earlier searching. Unless I've misunderstood what you are both saying. It seems very odd to me that this is not something documented clearly by Microsoft as surely it is of interest to anyone using SSIS.

To see if I could find out by experiment, I created a package writing to a particular directory. I then granted permission to that directory to the proxy account but not the agent service account. The job failed when trying to write buffer temp storage. I then granted permission to the agent account and this time files started appearing.

It would seem that it is the agent account that requires the permission, not the proxy.

But where is it trying to write buffer temp storage when no directory is specified in the package? The documentation suggests that it is the TEMP/TMP spec of the account but where is that pointing? I found what I could on the Internet and looked in all the possible places that might be but never saw any files being written in any of those places. And why, if it is the TEMP/TMP directory for the account does it not have permission to write there?