3

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?

Improve this question
7
  • 1
    Are all the tables in the same server? If so I suggest you do this in a stored procedure, not in SSIS.If you must do it in SSIS, then you need to put this increment in the iteration expression learn.microsoft.com/en-us/sql/integration-services/control-flow/… Commented Mar 3, 2019 at 2:26
  • Hi Nick, unfortunately the tables are not on the same server and my organization does not allow "linked-servers". The data is on a production box which I only have read access to and I am moving it to a Test server/database for the initial development and proof of concept. I have updated those fields in the For Loop container to specify the iterations but it does not seem to read the variable. Commented Mar 3, 2019 at 23:28
  • 1
    Not allowing linked servers is a good idea. So you definitely need to use SSIS. I reread your description and it sounds like you just need to put the dataflow inside the for loop. When you say things like "Does not seem" and your explanation is kind of vague, it really seems like you need to do more analysis of the problem. When you run the package interactively what happens? Does the loop not run repeatedly or do you not see the data you expect? Commented Mar 3, 2019 at 23:38
  • Hi, sorry Nick thought I mentioned that in my initial post. When placed inside the For Loop, everything "runs" successfully as in it turns green but no data is moved. So that leads me to believe that the variable is not being read or is available for the For Loop container. I have the variable scope set at the package level and have the @[User::month1] set for the InitExpression, @[User::month1] <= -49 for EvalExpression, @[User::month1] = @[User::month1] -1 for AssignExpression. Commented Mar 3, 2019 at 23:57
  • 1
    Try to set the Delay validation property of the dataflow to true Commented Mar 4, 2019 at 0:25

2 Answers 2

Reset to default
1

First of all, try to set the data flow Delay Validation property to True. If it still not working, instead of passing the variable as parameter in the OLEDB Source use expressions:

  1. Create a variable of type string.
  2. Change its EvaluateAsExpression property to True

  3. Set the expression similar to:

    "select * from table 
 Where column =" + (dt_str, 50)@[User::month1]

  4. In the OLEDB Source select the Access mode as SQL Command from variable and select this variable.

Be aware that the month1 variable is not created twice wuth different scopes, click on the data flow task and check the variable panel if it shows additional variables.

Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

I will give this a try and comment back if it works. I appreciate your response and suggestion! :)
That's a very good point about the same named variable in multiple scopes!!!! It's easy to do (especially in containers) and hard to pick up. I always make all of my variables at package level to stop this happening.
1

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!

Improve this answer

1 Comment

i removed the last sentence since it is not true. We all may make the same mistake. Don't forget to mark your answer as accepted and if you find the other answer helpful feel free to upvote it

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.