Two innocuous properties on every Data Flow in SSIS. You’ve likely never noticed them. But they can easily kill your jobs if not set properly.
They tell SSIS where to write to on disk under given circumstances. BufferTempStoragePath is where on disk it will write buffer contents in the event that there is no available memory. BLOBTempStoragePath is where on disk it will write any BLOB data in the buffer.
Why does this matter? Under normal circumstances, SSIS will write to the default location for these settings – a space in the executing users Documents and Settings folder. In Development, this rarely matters as developers will be executing packages as themselves, and will have rights to their Documents & Settings folders
In production, if you’ve deployed correctly (i.e. with a domain user executing the tasks) the domain user will not have that folder. So somewhere down the line your job may fail with the messages:
[DTS.Pipeline] Information: The buffer manager detected that the system was low on virtual memory, but was unable to swap out any buffers. 4 buffers were considered and 4 were locked. Either not enough memory is available to the pipeline because not enough is installed, other processes are using it, or too many buffers are locked.
[DTS.Pipeline] Error: 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.
It may happen tomorrow, it may happen next year, it may never happen at all. But if it does happen, you don’t want to go back and apply the fix to every data flow in every package in your solution. Trust me, I did this for 100 packages – it took me 2 days, bored the hell out of me and gave me a sore mouse finger. The good news is, it’s pretty easy to prevent. First up, give every package with a data flow a pair of variables at the package level:
Then, create a configuration that populates these variables with a valid filepath for writing this data to. You really want to put these on a separate disk to the databases, and preferably a fast local one. BufferTempStoragePath ideally should be on a different drive to wherever Virtual Memory is paged to – if Virtual Memory is being squeezed because of a disk space shortage, if you try to write buffers to the same drive, you’ll not get any disk space and the jobs will still fail.
Finally, in every Data Flow, set expressions for the BufferTempStoragePath and BLOBTempStoragePath properties to take their values from the corresponding variables. If you use templates, then these properties and configurations get carried across when you reuse the package, making this life saver easy to implement. A stitch in time saves RSI.