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: 246695 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.

If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

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: 246695 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!

If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

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....