Hi,

I have 150 ssis packages. all are built on sql serrver 2008 R2. For example I want to change DefaultBufferMaxRows to 100000 for all my packages in one shot or while executing the ssispackage I want use 100000 as my DefaultBufferMaxRow value how to and where to change the setting?

Thank you

Raj

• Edited by raj_kumar123 18 hours 49 minutes ago

Hi Raj, 

First of all, you can't change the property DefaultBufferMaxRows (or any other property) at execution time. In order to change it in one shot in your packages, for your version you have two ways: 

- Setting up the property when executing packages with a SQL Agent Job

- Using configurations to overwrite the property value. Then you just change the value in your table, xml file or whatever source you're using. 

Unfortunately, you'd have to change your 150 packages and apply the configuration before you deploy them again. 

Regards

Pau

Thanks Pau.

There is a file called Biml.xsd in C:\Program Files (x86)\Microsoft Visual Studio 10.0\Xml\Schemas\ location where we have the properties used in SSIS packages. Can't we change the setting here and re-run the package?

Thank you

Raj

The defaultmaxbuffer row property value is not stored in the file you have mentioned above. As Pau has mentioned in his reply, you can change the property value at runtime by reading from a table, XML configuration or calculate it as well but that would require you to change the existing packages and adding the component to update the property value.

An example Dynamically Setting DefaultBufferMaxRows

I understand what Pau mentioned in his reply but what I am saying is when you build the package the DefaultBufferMaxRows value which is 10000 is comming from the biml.xsd file so if we change it here does it work for all other packages too?

Hi Raj, 

are you generating all the packages automatically using BIML, then? AFAIK, Biml.xsd is used to load schemas to the BIDS Helper editor so you can enable Intellisense when editing your BIML code. 

If you are generating the packages automatically you may change your BIML scripts setting the property and re-generate the packages and then re-deploying them. 

Otherwise, you must use configurations or setting it up through the Agent when configuring the job.

Regards

Pau