"Access is Denied" for BLOBTempStoragePath for a long job
Hi All, I'm running DTSX packages programmatically via Microsoft.SqlServer.Dts.Runtime.Package. In the job in question I'm running one large job exporting some millions of records to CSV and in parallel I'm running smaller jobs doing the same for several thousands of records. The latter ones are also run in parallel with the limit of 3 concurrent jobs at a time. Eventually the large job throws "Access is Denied" exception claiming it cannot "create a file to spool a long object on the directories named in the BLOBTempStoragePath". I could trade-off performance but I would like to run smaller jobs in parallel rather than wait for large job to complete. Here is the full text of the exeption The errors were: 10/21/2010 5:57:56 AM: -2147024891: Access is denied. 10/21/2010 5:57:56 AM: -1073446887: The buffer manager could not get a temporary file name. The call to GetTempFileName failed. 10/21/2010 5:57:56 AM: -1073446893: The buffer manager could not create a temporary file on the path "C:\DOCUME~1\ovz\LOCALS~1\Temp\1". The path will not be considered for temporary storage again. 10/21/2010 5:57:56 AM: -1073450896: The buffer manager cannot create a file to spool a long object on the directories named in the BLOBTempStoragePath property. Either an incorrect file name was provided, or there are no permissions. 10/21/2010 5:57:56 AM: -1073442718: An error occurred while attempting to perform data conversion. The source type could not be converted to the destination type. 10/21/2010 5:57:56 AM: -1071607767: SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR. The "component "Derived Column" (1108)" failed because error code 0xC0049062 occurred, and the error row disposition on "output column "Copy of SAMPLE" (1192)" specifies failure on error. An error occurred on the specified object of the specified component. There may be error messages posted before this with more information about the failure. 10/21/2010 5:57:56 AM: -1073450974: SSIS Error Code DTS_E_PROCESSINPUTFAILED. The ProcessInput method on component "Derived Column" (1108) failed with error code 0xC0209029. The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running. There may be error messages posted before this with more information about the failure. 10/21/2010 5:57:57 AM: -1073450975: SSIS Error Code DTS_E_THREADFAILED. Thread "WorkThread0" has exited with error code 0xC0209029. There may be error messages posted before this with more information on why the thread has exited. 10/21/2010 5:58:01 AM: -1071607563: The component "DataReader Source" (1) was unable to process the data. 10/21/2010 5:58:01 AM: -1073450952: SSIS Error Code DTS_E_PRIMEOUTPUTFAILED. The PrimeOutput method on component "DataReader Source" (1) returned error code 0xC02090F5. The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing. There may be error messages posted before this with more information about the failure. 10/21/2010 5:58:01 AM: -1073450975: SSIS Error Code DTS_E_THREADFAILED. Thread "SourceThread0" has exited with error code 0xC0047038. There may be error messages posted before this with more information on why the thread has exited. The warnings were: 10/21/2010 5:58:59 AM: -2147381246: SSIS Warning Code DTS_W_MAXIMUMERRORCOUNTREACHED. The Execution method succeeded, but the number of errors raised (11) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors. O.Z.
October 21st, 2010 5:50pm
Open the package in BIDS and check the data flow task's "BLOBTempStoragePath" and "BufferTempStoragePath". Give appropriate path to these properties and make sure that the account under which the job is executing has write permissions to these paths.Nitesh Rai- Please mark the post as answered if it answers your question
October 21st, 2010 6:05pm
In my dtsx the "BLOBTempStoragePath" is empty. Judging from the output "C:\DOCUME~1\ovz\LOCALS~1\Temp\1" directory is used, which is fine by me and this same dtsx works correctly in other circumstances. But in this particular case (with parallelism involved) I eventually get the error I've described. Can it be caused by conflict between several dtsx Packages running simultaneously in the same AppDomain?O.Z.
October 21st, 2010 10:00pm
I have just had a similar issue, to resolve I set the antivirus solution (in my case it was McAfee) to exclude this directory from scanning and the error disappeared.Arthur My Blog
October 21st, 2010 10:44pm
Hi O.Z. Are you using SQL Server Integration Services 2005? If so, the issue you described seems to be a known issue, and had been fixed in Cumulative Update for SQL Server 2005 Service Package 3. You can have more information about this issue from: http://support.microsoft.com/kb/972365 So, please try to solve the issue by apply the latest hotfix. For SQL Server 2005, the latest hotfix is: Cumulative Update #12 for SQL Server 2005 Service Pack 3 For SQL Server 2008, the latest hotfix is: SQL Server 2008 SP2 Thanks, Jin ChenJin Chen - MSFT
October 25th, 2010 8:50am