for-loopsql-server-2012ssissql-server-2012-datatools

SSIS FOR Loop going into infinite loop

I believe I have set the expressions right, but it keeps going into infinite loop. Here are the details:

Variables:
@CD Datetime = @[User::ED]
@ED datetime = 3/31/2014
@M12 Datetime = DATEADD("d", 1, DATEADD("m", -12, @[User::ED] ))

For-Loop:
InitExpression: @CD = @ED
EvalExpression: @CD > @M12
AssignExpression: @CD = DATEADD("m", -1, @CD)

Within the For Loop is an Execute SQL task that inserts the @CD value for that iteration into a table. The assignment expression is somehow not working, and hence the EvalExpression condition is never met. What am I missing?

I am running SQL Server 2012. Thanks.

Solution

  • The Variable @[User::CD] has an Expression on it where it's assigned the value of @[User::ED]. You then attempt to modify the value of @[User::CD] within the context of a for loop. That doesn't throw an error like trying to modify the iterator outside the for construct like it would in C based languages but logically, it's wrong.

    Assume SSIS changes the value by decrementing it by 1. The next time it needs to reference @[User::CD], it will use the Expression which says my value is really @[User::ED] which never changes.

    Resolution - remove the Expression from your @[User::CD] variable.