I've run into a problem with a SQL Agent SSIS proxy apparently not loading the User Registry (and environment variables) of the proxy/credential. Situation: Some SSIS packages we have are set with BufferTempStoragePath="". The SSIS packages run fine when manually executed as the application's SSIS proxy, and the SQL Agent job will run fine when the SSIS proxy account is logged in via MSTSC. After looking at http://support.microsoft.com/kb/912911 , I added the proxy account in IIS_WPG (which had run as batch rights in the local security manager) so our configuratoin matches the KB article. Data connection configuration is loaded from a text configuration file. Problem: When running the SQL Agent job without the SSIS proxy account logged in interactively the job fails with "The buffer manager cannot create a temporary storage file on any path in the BufferTempStoragePath property." I suspect that this buffering has started happening due increased memory pressure on the server following virtualization of the hardware. From random (probably uninformed) web pages it seems that BufferTempStoragePath defaults to %TEMP%, which would be acceptable. I used psgetsid.exe (Sysinternals app) to find out the SID of the local machine proxy account, and when the SSIS SQL Agent job is running I don't see the registry hive loading in HKEY_USERS. If I do use runas to run notepad as the SSIS proxy account I see the registry hive for the user appear as expected. In our Test environment I've hardcoded the bufferTempStoragePath to be the proxy's temp directory on some packages and these run flawlessly, however I don't want to have to do that for all the packages (which I don't write) as it'd make them less portable. Question: Is it normal to not have the user registry loaded for the SSIS proxy? Is there some way for the proxy to access its environment variables if not? Any thoughts appreciated! Robert EDIT: Should have mentioned this is a SQL 2005 server

When running an SSIS job step type in Agent, it doesn't load a full user shell (even though it's executing under the user account). That's why it can't access the user registry or user environment variables. The simplest workaround ise to use a CmdExec step (that runs DTEXEC) instead of an Integration Services step in the Agent job. The CmdExec step does create a full user session, so it should work for this.John Welch | www.mariner-usa.com | www.agilebi.com | ssisUnit.codeplex.com

Thanks for the pointer John. I've created a new job and appended the commandline from the old SSIS steps onto DTExec.exe. It's running now (I can see dtexec.exe running as the proxy account in taskmgr) and I'll see if it's any better. I don't think xp_cmdshell is loading the proxy account's user registry though. I don't see the registry hive for the proxy account loading up, and creating a seperate xp_cmdshell job and putting in echo %TEMP% is returning c:\windows\temp rather than C:\Documents and Set... That said, the environment variables set up for xp_cmdshell might be good enough for my purposes. Robert

I have the same problem with .NET script components. They need to access the temp folder and the proxy account is given the temp folder of the SQL agent service account. My .NET code fails. So I tried John's idea. When running an SSIS job step type in Agent, it doesn't load a full user shell (even though it's executing under the user account). That's why it can't access the user registry or user environment variables. The simplest workaround ise to use a CmdExec step (that runs DTEXEC) instead of an Integration Services step in the Agent job. The CmdExec step does create a full user session, so it should work for this. But when I run it using DTEXEC, I still get the temp folder of the sql agent service account. So we don't seem to get a full user session... Can we enforce a full user session somewhere?