How to do Batch Process ?

Question

How to do Batch Process ?

prakashr.r7

SSCrazy

Points: 2828
More actions
April 25, 2013 at 4:45 am

#394669

Hi Friends,
I have to load 200,000 thousand records in a table. Since RAM usuage goes very high and i don't want to let RAM to use more memory , i need to do it by batch process. I know how to do using FOR LOOP and Record set Destination. But all i can do is doing row by row. How can i process 1000 records per loop?
I did this using T-SQL and while loop...since I made a column having dense rank number. using that column i am processing 1000 records per loop.....
But i don't have any idea how to do through SSIS? how to set loop to process 1000 records?
Any suggestions would be really appreciated
Thanks in advance

Viewing 5 posts - 1 through 4 (of 4 total)

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

Phil Parkin SSC Guru Points: 246964 More actions · Answer 1

By 'Load' what do you mean? Just INSERTs, or INSERTs & UPDATEs? Or something else?

If just INSERTs, SSIS should eat that up with no need for any 'batch processing' - just use fast load and it should all process quickly.

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

prakashr.r7 SSCrazy Points: 2828 More actions · Answer 2

Phil Parkin (4/25/2013)
By 'Load' what do you mean? Just INSERTs, or INSERTs & UPDATEs? Or something else?
If just INSERTs, SSIS should eat that up with no need for any 'batch processing' - just use fast load and it should all process quickly.

Yes, Phil....200,000 is just a number for SSIS.....but i have to process somtimes 2 to 5 million data....the problem is i am doing a Row_Number partition in SSIS...it kills the tempDB as you know it does its operation in tempdb....so i am thinking of doing by batch by batch process...

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

prakashr.r7 (4/25/2013)
Phil Parkin (4/25/2013)
By 'Load' what do you mean? Just INSERTs, or INSERTs & UPDATEs? Or something else?
If just INSERTs, SSIS should eat that up with no need for any 'batch processing' - just use fast load and it should all process quickly.
Yes, Phil....200,000 is just a number for SSIS.....but i have to process somtimes 2 to 5 million data....the problem is i am doing a Row_Number partition in SSIS...it kills the tempDB as you know it does its operation in tempdb....so i am thinking of doing by batch by batch process...

You started off talking about RAM and now you're mentioning TempDb - it's a little confusing.

What do you use the row number for? Is it for selection purposes, or for output purposes?

There may be ways to do what you are trying to achieve which are more efficient than what you have currently - if you provide a bit more background to describe your requirements, you may get some useful suggestions.

Also, you did not answer my question!

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

prakashr.r7 SSCrazy Points: 2828 More actions · Answer 4

Phil Parkin (4/25/2013)
prakashr.r7 (4/25/2013)
Phil Parkin (4/25/2013)
By 'Load' what do you mean? Just INSERTs, or INSERTs & UPDATEs? Or something else?
If just INSERTs, SSIS should eat that up with no need for any 'batch processing' - just use fast load and it should all process quickly.
Yes, Phil....200,000 is just a number for SSIS.....but i have to process somtimes 2 to 5 million data....the problem is i am doing a Row_Number partition in SSIS...it kills the tempDB as you know it does its operation in tempdb....so i am thinking of doing by batch by batch process...
You started off talking about RAM and now you're mentioning TempDb - it's a little confusing.
What do you use the row number for? Is it for selection purposes, or for output purposes?
There may be ways to do what you are trying to achieve which are more efficient than what you have currently - if you provide a bit more background to describe your requirements, you may get some useful suggestions.
Also, you did not answer my question!

Phil, i am doing insert....row number is to get the output not for selection ....

What i am trying to do with Row_number partition is (I explained with SeqNo field below)

for Example :

ID Name Address SeqNo

1 SAM 45 Main ST 0

1 SAM 50 POST BOX 1

2 SEAN HILLS ROAD 0

3 SETH SALT WATER ST 0

3 SETH NEW BERLIN ST 1

since i am having huge data , if i do it at one shot...the temp db is getting affected.. so i think of doing batch process....