April 13, 2013 at 3:10 am
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 😀
April 13, 2013 at 3:12 am
We are using SQL Server 2008 (R2) by the way
September 6, 2013 at 2:17 pm
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.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply