May 13, 2014 at 6:58 am
Hello, I have a package that has been working perfectly. It has an Execute SQL task that reads three fields from a pair of tables and saves the "Full result set" into a variable of type object. Then it connects to the Foreach container which parses the object into three variables. For most of last week, the Execute SQL task and Foreach container were working perfectly; the control flow was entering the container and there it performed work on each row from the query.
However, after putting the project down for a couple of days I returned to it last night and discovered that while the Execute SQL task was working (i.e., it is colored green when debugging), control was not being sent to the Foreach container. This morning I deleted the Execute SQL task and recreated it exactly as it was before and it began working again; control was being sent to the Foreach container (which worked on each row of data). Unfortunately, five minutes later the Execute SQL task continued to light up green but the Foreach container is being ignored.
I haven't changed the query in my Execute SQL task, and this query returns many rows when running in SSMS. Has anyone encountered a situation where the Foreach container suddenly won't do anything, when before it was? Thanks.
May 13, 2014 at 7:38 am
OK, the problem is that I had a disabled task that was also connected to the same Foreach container. I have been trying to change my package so that it operates on a full dataset unless a parameter is specified when creating the job on the SQL server. In that case, I want the Foreach loop to operate only on a single row.
If only one Execute task is connected to the Foreach container control flow enters it and every thing is fine. If a second Execute task is connected to the Foreach container, even if it is disabled, and even if the first Execute task is the one that gets the green color, control flow does not enter the Foreach container.
SSIS is full of surprises.
May 13, 2014 at 12:57 pm
The issue you are describing is because of how you have the precedent constraints configured.
You have each Execute SQL Task connected to the For Each Loop Container with a constraint with an Evaluation of Success. By default when a object has multiple tasks before it the default is for the constraints to work as a Logical AND, meaning both must evaluate to True. You either need to change the Constraint Options to change evaluation operation or you need to change the way multiple constraints are evaluated to OR so that if one of the previous tasks completes successfully the next step will continue.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
May 14, 2014 at 6:13 am
Hi Jack, thanks for the clarification. I changed my logic and found a different way to pass in a parameter to the package allowing me to operate on a single row if the parameter comes in.
I understand what you're saying about both precedent constraints acting as an "AND". When one of the branches is disabled it is tempting to think that the precedence constraint leading to it is not being considered, but obviously it is.
Thanks for shedding light on the issue. 🙂
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply