Wednesday, 10 February 2016

What is BufferTempStoragePath Property in SSIS, Configure Dynamically

What is BufferTempStoragePath Property in SSIS? 
What is the default location of this property?

When a source(OLE DB Source etc) is pulling in data, it places them into buffers, one row=one buffer. And the memory location used for storing the buffer can be changed by setting the propety BufferTempStoragePath at data flow level. so BufferTempStoragePath is the file system path used to temporarily cache buffer data.

Now question is that what is the default location used for store buffer data?
By default the BufferTempStoragePath is mapped to the user running the package's Documents and Settings folder.  This is problematic when numerous packages are running simultaneously and using this disk location, and you don't have a large disk for your C: drive.

Now the question is that what we will do if C drive size is very less?
To overcome disk size problem we have BufferTempStoragePath, However the property is specific to a data flow task, so this would require developers to change the property is every data flow task of every package.

Now the question is that who we can dynamically change of value BufferTempStoragePath?
Of course that is possible, using Package Configurations you can do it.

1.      Create a sample package with 2 or 3 Dataflowtask
2.      By default all BufferTempStoragePath are blank
3.      Go to Package Configurations
a)      Add
b)      Configuration type: SQL Server
c)      Select your connection manager
d)     Create the table SSIS Configurations
e)      Configuration Filter: TempPathDefault
f)       Click Next
g)      Expand Package
§  Executables
§  Data Flow Task 1
§  Properties

§  Check BufferTempStoragePath



Post a Comment

Find job here...