BLOBTempStoragePath and BufferTempStoragePath

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:

Control::strBufferTempStoragePath
Control::strBLOBTempStoragePath


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.

About BI Monkey

Comments

6 Responses to “BLOBTempStoragePath and BufferTempStoragePath”
  1. ETL vs ELTL says:

    I have never heart about this property and after reading your article I felt that this property is very helpful if you are dealing with huge data.

    Thanks for your helpful post.
    Manish

  2. Arvind Ravish says:

    Can we not change the System variable TMP path which is referenced by the BufferTempStoragePath configuration within the Data flow instead of changing every single data flow task in the solution?

  3. BI Monkey says:

    Possibly (I’d need to validate that) – but it’s not a very transparent solution – i.e. the solution is not visible in the package itself.

Trackbacks

Check out what others are saying about this post...
  1. [...] Part of any SSIS development experience inevitably results in you discovering a minor mistake or something that was missed a long way into the development cycle – or even after, in testing (you do test your code, right?). Then you are faced with the tedious job of opening every single package, making a change in every one… and getting some serious mouse finger. Much like I once did when I learned about BufferTempStoragePath. [...]

  2. [...] to James Beresford’s article (BI Monkey) each data flow in every package should have the BLOBTempStoragePath and BufferTempStoragePath [...]

  3. [...] post on why you should override that value, even if you don’t anticipate needing it http://www.bimonkey.com/2008/04/blobtempstoragepath-and-buffertempstoragepath/ Tagged: questionssql-server-2005ssis /* * * CONFIGURATION VARIABLES: EDIT BEFORE [...]



Speak Your Mind

Tell us what you're thinking...
and oh, if you want a pic to show with your comment, go get a gravatar!