0

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.

Meta747
  • 253
  • 1
  • 16
  • @billinkc updated main post. Thank you – Meta747 Jul 16 '14 at 14:29
  • Just so I'm clear. The Variable @[User::CD] has an Expression on it where it's assigned the value of @[User::ED]. You then use a Forloop where CD is initialized to ED and the decremented by month/minute until you cross the M12 boundary – billinkc Jul 16 '14 at 14:40

1 Answers1

0

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.

billinkc
  • 59,250
  • 9
  • 102
  • 159