I'm reaching out to the experts as I have hit a wall with a recent project. I have created an SSIS package (2008R2) that uses a script task to build a SQL statement, where a variable(@month1) is being used within the SQL statement, to specify a month look back in a membership table. I want to also use the @month1 variable as a "counter" for the loop container to specify how many times to execute the query. The SQL query is attached to a data flow task to append these records into a table on a SQL server database. The script task and data flow task work outside of the for loop container with the initial value given for the @month1 variable but I cannot figure out how to make the for loop container update the @month1 "counter" variable so that the for each loop can use it as a "counter" and the SQL statement can use it as a condition with in the created SQL statement. Any one have any ideas or examples on how to do this?
** Update ** The For Loop container is the issue. The script task and data flow task work outside of the For Loop container. It will use the initial variable setting for @month1 and create the dynamic sql script, execute script and transfer data from source database server to the destination source server. The issue is when I place these steps within the For Loop container, the container executes and turns green but does not invoke the steps within it. This is why I'm thinking the container is not reading the variable @month1, even though the variable is set at the package level. Any thoughts?
I appreciate everyone's responses but it seems I tricked myself on this one. In looking for the most complicated issue I overlooked the most simple and obvious one. The reason my For Loop was not executing the steps inside of the container was because I had the initial value for @month1 set to 3 (intentionally) and wanted to loop until it was resolved to -49. In the EvalExpression setting, it will evaluate until the statement is FALSE...so the evaluation I had in there of @month1 <= -49 was already false. It needed to be @month1 > -49 so as soon as it fell to -49 the statement would be false. I do this to myself more than I should admit, can't see the forest for the trees!