Batch Size Setting ?

Question

Batch Size Setting ?

homebrew01

SSC Guru

Points: 55548
More actions
September 7, 2010 at 2:59 pm

#239957

I used the Wizard to copy a table from ServerA to ServerB. Then I thought I could edit the SSIS package through BIDS to set the batch size. But my OLEDB connections don't seem to be the same type as the ones I read about.
If I go to "Connection Managers" and edit "DestinationConnectionOLEDB", then choose the "ALL" option, I have settings such as:
Extended Properties
Locale Identifier
Connect Timeout
General Timeout
File Name
OLE DB Services
If I right-click properties on "DestinationConnectionOLEDB", I get a pane on the right with:
ConnectionString
Data Source ID
Delay Validation ?
Where is this "Batch Size" Setting ?

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Alvin Ramard SSC-Forever Points: 41190 More actions · Answer 1

homebrew01 (9/7/2010)
I used the Wizard to copy a table from ServerA to ServerB. Then I thought I could edit the SSIS package through BIDS to set the batch size. But my OLEDB connections don't seem to be the same type as the ones I read about.
If I go to "Connection Managers" and edit "DestinationConnectionOLEDB", then choose the "ALL" option, I have settings such as:
Extended Properties
Locale Identifier
Connect Timeout
General Timeout
File Name
OLE DB Services
If I right-click properties on "DestinationConnectionOLEDB", I get a pane on the right with:
ConnectionString
Data Source ID
Delay Validation ?
Where is this "Batch Size" Setting ?

I'm assuming you have an OLEDB Destination and the Data Access Mode is set to Table or View. Try changing that to Table or View - Fast Load.

Alvin Ramard
Memphis PASS Chapter[/url]

All my SSC forum answers come with a money back guarantee. If you didn't like the answer then I'll gladly refund what you paid for it.

For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]

sql.abhishek Old Hand Points: 381 More actions · Answer 2

Hi

Yes go to OLEDB Destination tab and put Data access mode = Table or view – fast load then u will have ur options like Rows per batch and Maximum insert commit size.

Regards

abhi

Phil Parkin SSC Guru Points: 246960 More actions · Answer 3

sql.abhishek (9/7/2010)
Hi
Yes go to OLEDB Destination tab and put Data access mode = Table or view – fast load then u will have ur options like Rows per batch and Maximum insert commit size.
Regards
abhi

Why does the image of a parrot spring to mind? :hehe:

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

sql.abhishek Old Hand Points: 381 More actions · Answer 4

I am trying to help this guy in a more specific way. No offense but the gentleman above me was assuming things and giving the approx answers!!!!

thts y Mr. Phil....:-)

Alvin Ramard SSC-Forever Points: 41190 More actions · Answer 5

sql.abhishek (9/7/2010)
I am trying to help this guy in a more specific way. No offense but the gentleman above me was assuming things and giving the approx answers!!!!
thts y Mr. Phil....:-)

And how was your answer better than mine?

Alvin Ramard
Memphis PASS Chapter[/url]

All my SSC forum answers come with a money back guarantee. If you didn't like the answer then I'll gladly refund what you paid for it.

For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]

sql.abhishek Old Hand Points: 381 More actions · Answer 6

Hey Alwin,

I never said mine is better or urs is better ... i was supporting ur answer and in addition to that i tried to give him some exact things which will be there in BIDS..

Once again no offense to you but why are we arguing on this not so important thing.

Regards

homebrew01 SSC Guru Points: 55548 More actions · Answer 7

OK ... I think I have it .... I was looking at the "DestinationConnectionOLEDB" tab by mistake Thanks

I want to copy 40,000,000 records, so I'm setting the batch size to 50,000 to keep it manageable.

I don't quite understand what the "Maximum insert commit size " setting does.