SSIS : Final Rowcount different than "Row per batch" and "Max Insert Commit Size"

%3CLINGO-SUB%20id%3D%22lingo-sub-242086%22%20slang%3D%22en-US%22%3ESSIS%20%3A%20Final%20Rowcount%20different%20than%20%22Row%20per%20batch%22%20and%20%22Max%20Insert%20Commit%20Size%22%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-242086%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20to%20transfer%20an%20hundred%20millions%20of%20rows%20from%20one%20SQL%20instance%20to%20another%20one%20(both%20are%20SQL%202016%20SP2)%2C%20so%20I%20tried%20to%20tune%20at%20minimum%20the%20Dataflow%20by%20defining%20both%20the%20Target%26nbsp%3B%26nbsp%3B%22Row%20per%20batch%22%20and%20%22Max%20Insert%20Commit%20Size%22%20to%2010000.%20My%20understanding%20was%20that%20these%20parameters%20have%20the%20same%20behavior%20that%20BatchSize%20for%20a%20Bulk%20Insert%20statement%2C%20so%20I%20expected%20to%20find%20the%2010000%20value%20in%20the%20rowcounts%20columns%20from%20my%20SQL%20Profiler.%26nbsp%3B%3C%2FP%3E%3CP%3EI%20can't%20understand%20why%20I'm%20retrieving%20the%20value%203889%20instead%20of%20the%20expected%2010000%20when%20I%20run%20my%20SSIS%20package%20(ref.%20attached%20Screenshot).%20Any%20idea%20%3F%3C%2FP%3E%3CP%3EThanks%20for%20your%20help.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-242086%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EIntegration%20Services%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EPerformance%20Issues%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E
Occasional Contributor

Hi, 

I have to transfer an hundred millions of rows from one SQL instance to another one (both are SQL 2016 SP2), so I tried to tune at minimum the Dataflow by defining both the Target  "Row per batch" and "Max Insert Commit Size" to 10000. My understanding was that these parameters have the same behavior that BatchSize for a Bulk Insert statement, so I expected to find the 10000 value in the rowcounts columns from my SQL Profiler. 

I can't understand why I'm retrieving the value 3889 instead of the expected 10000 when I run my SSIS package (ref. attached Screenshot). Any idea ?

Thanks for your help. 

0 Replies