Problem: periodically, one of my SSIS packages was throwing an error saying "The buffer manager cannot create a temporary storage file on any path in the BufferTempStoragePath property. There is an incorrect file name or no permission." The package in question then would hang, locking a file that it was trying to import.

Solution: The BufferTempStoragePath is kind of self explanatory. As usual, Jamie Thomson’s SQL Junkie blog has an excellent article that gives useful background on the issue, in Dataflow mechanics. He also points to my other favorite SSIS reference, Microsoft SQL Server 2005 Integration Services, by Kirk Haselden. Apparently my package needed to write some data out to the buffer and did not have proper permissions, just as the error says. So, where is this buffer? Neither THomson nor Haselden address this, but Haselden does answer an asked question: why does my package usually succeed, only rarely failing? “The Data Flow Task only uses the BufferTempStoragePath when it runs out of memory and must spool buffers out to disk.” (p422)

In a random forum I found someone mentioning that the default buffer location is in c:\documents and settings\<username>\.... Well, there’s my problem — the user executing the package does not have such a directory. No wonder it failed. Thankfully there is an override at the package level: you can directly set the path in a Data Flow diagram’s properties.

Posted with : Tech, Microsoft SQL Server and other databases, SQL Server, SQL Server Integration Service