Permission error while executing from SQL Server Agent job

Topics: Issues 3: Executing the Component (Run-Time)
Jan 27, 2012 at 4:43 PM
Edited Jan 27, 2012 at 4:47 PM

I am using v1.6 of the Dimenstion Merge SCD component (Dimension Merge SCD Setup 2008 x64.msi)and I am suddenly getting this error when running my package from a SQL Server Agent job:

A problem occurred reading the metadata for the Slowly Changing Dimension component: Access to the temp directory is denied.  Identity 'HQ\bi_ssis_job_prod' under which XmlSerializer is running does not have sufficient permission to access the temp directory. CodeDom will use the user account the process is using to do the compilation, so if the user doesnt have access to system temp directory, you will not be able to compile.  Use Path.GetTempPath() API to find out the temp directory location.  Metadata will be regenerated, but some settings may be lost.  If you also receive an error message stating the component is 'Unable to generate a temporary class', then you may need to grant the account executing the package rights to the Windows Temporary folder.

I am not aware of any permissions that have changed in my production environment so I am wondering what could have provoked this failure. After granting the user id full administrative privileges, the job runs successfully. Any insight into the cause for the above error would helpful.

Thanks,

Ami

Coordinator
Mar 26, 2012 at 4:28 PM

The problem is what the error is describing.  It's likely that the account doesn't have permission to write to the TEMP folder - likely %WINDIR%\Temp.  That's a little odd, because I only thought scripts required access to that location, but apparently not.  That's why when you elevated the account, it worked.  Try reducing the permissions again, but grant read/write access to the temp folder.