To limit the amount of records you can by simply tweaking the buffer size: DefaultBufferMaxRows a DFT (Data Flow Task) property that specifies a maximum number of rows that can be held in an individual buffer. By default the value is set to 10,000, though this figure will be reduced if (based on row size estimates) the memory consumption would be greater than the DefaultBufferSize. so something is wrong there in your settings. Set rows per batch here: http://www.google.ca/imgres?q=oledb+buffer+ssis&hl=en&safe=off&biw=1280&bih=814&tbm=isch&tbnid=NweV5w-kpTAJdM:&imgrefurl=http://technet.microsoft.com/en-us/library/dd537533%28v%3Dsql.100%29.aspx&docid=ThR84qSI0wVEeM&imgurl=http://i.technet.microsoft.com/dynimg/IC272630.jpg&w=655&h=640&ei=1Qu9T6jBA4Og2gWinbybDw&zoom=1&iact=rc&dur=346&sig=117639262121657972501&page=1&tbnh=146&tbnw=150&start=0&ndsp=24&ved=1t:429,r:13,s:0,i:97&tx=99&ty=77 to a small value.Arthur My Blog

I have a for loop that has two variables, variableX and variableY passed to it by two execute sql tasks. Within the for loop my EvalExpression is variableX <= variableY. Within the For Loop I have a data flow that takes the bottom N number of records and processes them, then it loops around and processes the next N number of records. After each loop it updates a table with a value that I'm grabbing variableY from so it knows which records to grab next. The problem is, I can't seem to figure out how to update variableY after each loop. So the package runs fine but it errors out at the end because it tries to process even though there are no records left to process. It's seems like it should be pretty simple to solve, but I haven't had much luck. I tried updating the variable after the data flow with another execute sql task, but that doesn't seem to work. Any help would be much appreciated!

VariableY gets loaded only once when the ForEach loop commences, does not get re-evaluated, this I guess is your issue, thus, the remedy seems is in setting it up only once before the loop starts. Why if ariableX <= variableY is the condition the Y suddenly need to change later?Arthur My Blog

Hi, to set a variable you can use a script task: SSIS: Reading and Writing to Variables in Script Task David.

VariableY gets loaded only once when the ForEach loop commences, does not get re-evaluated, this I guess is your issue, thus, the remedy seems is in setting it up only once before the loop starts. Why if ariableX <= variableY is the condition the Y suddenly need to change later? Arthur My Blog Thanks for the response ArthurZ. That is the issue, I need a way to update variableY after each loop. I need to update it based on a value in a table that gets updated in the data flow task. I've tried an execute sql task but it didn't seem to work. I will see if dac03's Script task is an option.

You cannot - ForEach simply caches it. Thus, yes, use the Script task, but the question is why. Why, once you know the record count, you may not loop until all of them have been processed? This is your upper bound (limit) to loop. The upper limit should NOT need to be changed. If you want to store interim processing data then consider adding one more variable that does not participate on the ForLoop set up.Arthur My Blog

You cannot - ForEach simply caches it. Thus, yes, use the Script task, but the question is why. Why, once you know the record count, you may not loop until all of them have been processed? This is your upper bound (limit) to loop. The upper limit should NOT need to be changed. If you want to store interim processing data then consider adding one more variable that does not participate on the ForLoop set up. Arthur My Blog I originally had just a data flow task that would process all records at once (about 7 million per day that join to another table that has about 150,000,000 records), but I've ran into memory issues that crash our server the job is running on. To get around this I'm just processing 100,000 records at a time until all records have been processed, hence the for loop. This has solved our memory issues and I'm able to process the records every day, it's just that i can't get the loop to finish successfully. I just get an error that says I can't insert a null value into the table that variableY is stored in. So the records are still processing ever day my job just ends with an error. Does that answer why I'm looking to do it this way or is there an easier way that I'm missing?

To limit the amount of records you can by simply tweaking the buffer size: DefaultBufferMaxRows a DFT (Data Flow Task) property that specifies a maximum number of rows that can be held in an individual buffer. By default the value is set to 10,000, though this figure will be reduced if (based on row size estimates) the memory consumption would be greater than the DefaultBufferSize. so something is wrong there in your settings. Set rows per batch here: http://www.google.ca/imgres?q=oledb+buffer+ssis&hl=en&safe=off&biw=1280&bih=814&tbm=isch&tbnid=NweV5w-kpTAJdM:&imgrefurl=http://technet.microsoft.com/en-us/library/dd537533%28v%3Dsql.100%29.aspx&docid=ThR84qSI0wVEeM&imgurl=http://i.technet.microsoft.com/dynimg/IC272630.jpg&w=655&h=640&ei=1Qu9T6jBA4Og2gWinbybDw&zoom=1&iact=rc&dur=346&sig=117639262121657972501&page=1&tbnh=146&tbnw=150&start=0&ndsp=24&ved=1t:429,r:13,s:0,i:97&tx=99&ty=77 to a small value.Arthur My Blog