“BatchSize” in SSIS

  • 30 November 2023
  • 0 replies
  • 202 views

Userlevel 1
Badge

In general, it's important to keep in mind that when using our CData drivers alongside various tools, one must consider the configurations and properties of these tools, as they can potentially override drivers' configurations.  

 

The "BatchSize" connection property in our drivers refers to the number of records committed as a batch to the server during INSERT/UPDATE/DELETE operations (for data sources that support batches).  

 

When configuring this property, it's important to consider the value of the “BatchSize” within the SSIS component itself. You can find this property in the 'Advanced Editor' window of the destination component. Follow these steps to access and modify it:  

 

  1. In the SSIS Data Flow task, right-click on the “CData {datasource} Destination” component;  

  1. Click on the option labeled “Show Advanced Editor...”;  

  1. In the “Advanced Editor” window click on the “Component Properties” tab;  

  1. In the “Custom Properties” section, you will find the “BatchSize” property listed;  

 

  

The correlation between these two properties:  

 

When the SSIS “BatchSize” property has a lower value than the corresponding property in our driver, the batches will only include a number of records up to the limit specified by the SSIS property. Conversely, if the SSIS "BatchSize" property value is higher than the "BatchSize" property of our driver, the driver's setting will take precedence.  

For example, when the SSIS property is configured as 1,000 and the corresponding driver property is set to 10,000, the batch size transmitted to the server will be 1,000 records. Conversely, if the SSIS property is set at 1,000 and the driver property is configured as 500, the batch size sent to the server will be 500 records.  

  

“BatchSize” property in SSIS  

“BatchSize” property in the driver settings  

Precedence  

The batch size transmitted to the server  

1,000  

10,000  

SSIS  

1,000  

1,000  

500  

Driver  

500  

  

Please reach out to support@cdata.com if you have any questions.  

 


This topic has been closed for comments