How to increment in SSIS For Loop in steps greater than one and not miss the orphans?

Question

I have a FOR loop in my SSIS package and it iterates over dates. However, that loop iterates one day at a time. I know I can do greater than one steps, but then what will happen is that if my steps are odd numbered them a date range with an even number of days will not be fully covered by the loop. For example: If my date range is for 4 days and I want to increment in three day steps, then only one iteration will happen and the fourth day will be missed.

Are there some For loops setting that I'm just missing or is there a special configuration that i have to set?

To get around it, I am just passing in the full range and doing the stepping inside the stored procedure, but that seems clunky if I'm using SSIS.

Any advice appreciated.

---

--ACG

Answer 1

I don't understand what you're expecting to have happen.  If you have a date range of four days, and you want to step on every third day... then your loop should hit the first and last day of your range.

You can use a For Loop and use @[User::YourDateVariable] = DATEADD("day", 3, @[User::YourDateVariable) to increment the variable.

---

Talk to me now on

Answer 2

Let me be clearer. Let's say I want to retrieve data for 1/1/2012, 1/2/2012, 1/3/2012 and 1/4/2012. If my Step=3 then the ForLoop task will get data for the first three days by passing in 1/1/2012 - 1/3/2012, but on the next iteration the range would be 1/4/2012 - 1/6/2012, which is 2 days too much. My question then becomes, is there a way to configure the for loop to set the 2nd iteration range to be 1/4/2012 - 1/4/2012.

Overall date ranges can vary, like, I could be retrieving data for 1/1/2012 to 1/25/2012. So I shouldn't have to change the step number to accommodate the range. I want the last iteration to be smart. If not possible, no biggie.

---

--ACG

Answer 3

Hi ACG,

I am afraid it's impossible to change iteration range dynamically in For Loop container, I suggest you can use script task to filter the date that you do not need. For more information about Script Task, please see: http://msdn.microsoft.com/en-us/library/ms141752.aspx

Thanks,
Eileen

Answer 4

Oh - OK.  I understand what you're looking for now.

You should be able to solve this just by using another variable (or modifying the use of one you already have).

You've currently got four variables in play - your "LowerLimit" and "UpperLimit" (Jan 1 and Jan 4, respectively).  You use those variables to set up your For Loop with the help of another variable, I'll call it "RangeStart".  On the first run of the Loop, RangeStart will be Jan 1.  You seem to have another variable in there - I'll call it "RangeEnd" that you also set in the For Loop evaluation to add 3 days to RangeStart.

I'd stop doing that.

Instead, select RangeEnd and open the properties window (F4).  Set EvaluateAsExpression to true.  Now set the expression to:

(DATEADD("day", 3, @[User::RangeStart]) > @[User::UpperLimit]) ? @[User::UpperLimit] : DATEADD("day", 3, @[User::RangeStart])

That expression will check to see if adding three days to RangeStart will make it fall after your upper limit.  If it does, it'll set RangeEnd to be the upper limit.  If not, it'll set RangeEnd to be three days after RangeStart.

---

Talk to me now on