Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


BlobTempStoragePath and BufferTempStoragePath - Default Location Change


BlobTempStoragePath and BufferTempStoragePath - Default Location Change

Author
Message
amcalpine
amcalpine
Grasshopper
Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)

Group: General Forum Members
Points: 13 Visits: 157
Hi SQL Experts

We have an issue at the moment where we are encountering the following error on our Pre-Prod environment during execution of our SSIS ETL packages. The error is as follows.

Failed to retrieve long data for column "XML_Column"

I believe this is a capacity issue as the C: drive has only 2gb free from 30gb partition. I think the XML Blob data is getting buffered on the C: drive during transit.

The setting for the BlobTempStoragePath and BufferTempStoragePath in our SSIS package is @[User::TempStorage].

Am I correct in assuming that this will be the TEMP location for whatever windows account is running the SSIS package i.e the SSIS Proxy account? The reason I ask is that while we are waiting for the 3rd Level Wintel team to clear down some space we attempted to change the Windows Environmental variables for the Proxy and Service account to point at our D: drive (much more space) but this had made no difference. I suspect that the C: drive is still getting hit. Maybe i'm missing something?

I'm hoping that the Wintel team can clear enough space for the short term but this will no doubt be consumed within time. I'm looking at an alternative to having to hard code a location into the BlobTempStoragePath and BufferTempStoragePath settings for each data flow task in all SSIS packages in all our environments which would be a major pain.

Any advice would be greatly appreciated.

Many thanks

Alan :-D



amcalpine
amcalpine
Grasshopper
Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)

Group: General Forum Members
Points: 13 Visits: 157
We are using SQL Server 2008 (R2) by the way



John Mihalko
John Mihalko
Forum Newbie
Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)

Group: General Forum Members
Points: 4 Visits: 133
amcalpine (4/13/2013)
The setting for the BlobTempStoragePath and BufferTempStoragePath in our SSIS package is @[User::TempStorage].
Am I correct in assuming that this will be the TEMP location for whatever windows account is running the SSIS package i.e the SSIS Proxy account?

Yes, you are correct unless you are executing the packages with a domain user, domain users do not have the folder 'C:\users\[username]\AppData\Local\Temp\'

http://www.bimonkey.com/2008/04/blobtempstoragepath-and-buffertempstoragepath (Monday, April 7, 2008)
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.

amcalpine (4/13/2013)
I'm looking at an alternative to having to hard code a location into the BlobTempStoragePath and BufferTempStoragePath settings for each data flow task in all SSIS packages in all our environments which would be a major pain.

I would highly recommend not hard coding these locations. I would suggest using the package configurations setting to populate the variables. That is what we do. We store our configuration settings in a SQL Server database. In that db we have tables for global settings (i.e. configurations for connection managers) and tables for the individual configuration settings for each solution, that way, when your path changes, you only have to change it in one place without opening any packages and redeploying.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search