Is there any releavance for "ROW PER BATCH" AND "MAX INSERT COMMIT SIZE" IN SSIS PACKAGES?
<p>I've have SSIS Package that is exporting 2.5 GB OF DATA containing 10 million records into Sql Server Database which has 10 partitions  including PRIMARY FILE GROUP. </p>

<p>Before Changing default <strong>Max Insert Commit size</strong> i.e."2147483647" and <strong>Row per batch</strong>.It was taking 7 mins for completed transformation with fast load option.</p>

<p>But After chaning it some decent value with some formula, the execution was done in only 2 minutes.</p>

<p>FYI- <strong>DefaultMaxBufferRows</strong> &amp; <strong>DefaultMaxBufferSize</strong>  were  default value in both scenorio i.e. 10000 and 10 MB  respectively.</p>

<p>To calculate <strong>Max Insert Commit size</strong> &amp; <strong>Row per batch</strong>
Below calucation are used.</p>

<p>1) Calculated length of records from source that is being transfered. which comes around 1038 bytes.</p>

<pre><code>CREATE TABLE [dbo].[Game_DATA2](
    [ID] [int] IDENTITY(1,1) NOT NULL, -- AUTO CALCULATED
    [Number] [varchar](255) NOT NULL, -- 255 bytes
    [AccountTypeId] [int] NOT NULL, -- 4 bytes
    [Amount] [float] NOT NULL,-- 4 bytes
    [CashAccountNumber] [varchar](255) NULL, -- 255 bytes
    [StartDate] [datetime] NULL,-- 8 bytes
    [Status] [varchar](255) NOT NULL,-- 255 bytes
    [ClientCardNumber] [varchar](255) NULL -- 255 bytes
)
</code></pre>

<p>2) Rows per batch = packate_size/bytes per record =32767/1038 =32 approx.</p>

<p>3) Max insert commit size = packate size *number of transaction = 32767*100=3276700
(Packate size and number transaction are variable can change as per requirement)</p>

<p>Question : </p>

<ul>
<li><p>Is there any relevance of rows per batch and max insert commit size? As there's no information mentioned in an archive <a href="https://technet.microsoft.com/library/Cc966529" rel="noreferrer" title="article">article</a> for tunning DFT(DATA FLOW TASK) execution.</p></li>
<li><p>Are these configuration works along with DefaultBuffermaxzie and<br>
DefualtBuffermaxrows?if yes how?</p></li>
</ul>