Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

BlobTempStoragePath and BufferTempStoragePath - Default Location Change Expand / Collapse
Author
Message
Posted Saturday, April 13, 2013 3:10 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, September 25, 2014 3:36 AM
Points: 13, Visits: 156
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



Post #1442021
Posted Saturday, April 13, 2013 3:12 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, September 25, 2014 3:36 AM
Points: 13, Visits: 156
We are using SQL Server 2008 (R2) by the way


Post #1442022
Posted Friday, September 6, 2013 2:17 PM


Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, December 4, 2014 2:35 PM
Points: 3, Visits: 80
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.
Post #1492415
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse