November 20, 2009 at 11:35 am
Hi all, i have a very basic kind of problem.
I use 2 DataFlow tasks.
The First, Task "A", is gathering basic data
The Second, Task "B" is executed in a foreach block iterating over each row produced by Task A to fetch detail data about these rows.
I store a relevant id of the rows of Task "A" in a Variable called User::FirstIdRunner.
I checked via a breakpoint and a that variable is set to the correct value each time.
Inside Task B, I use a different variable to construct a SQL query, containing that id, like this (unimportant parts skipped)
SELECT
[..]
where
BK.AuftragID = "+ (DT_WSTR, 50)@[User::FirstIdRunner] + "
and [..]
order by [..]
now i have the problem that in this query the variable FirstIdRunner is always 0.
I assume that the expression, though it's variable is set to the scope of Task B, is evaluated before "FirstIdRunner" is set to anything meaningful and does not get re-evaluated.
The question now is: is there a way to force the re-evaluation of the expression within Task B?
November 20, 2009 at 1:13 pm
Where are you entering the text of the SQL query containing the variable?
November 20, 2009 at 2:49 pm
it's inside a second variable, scope of Task "B"
The variable contains a expression with a standard query containing exactly one variable... the one mentioned above
November 20, 2009 at 2:53 pm
So the SQL command text is in a variable. How are you executing the command?
--edit
Are you executing the command through the use of an Expression?
November 20, 2009 at 3:14 pm
Sql is executed as "SQL from a variable".
The variable is a expression and contains the variable "FirstIdRunner" which is obtained from Task "A"
November 20, 2009 at 3:22 pm
I suggest that you change your approach slightly.
To get the variable evaluated as you want, you need to put the SQL into an expression (and the expression can use your variables).
November 20, 2009 at 4:37 pm
well yes, this is, as i explained, exactly what I'm doing
November 21, 2009 at 12:44 am
I guess I'm just not understanding what you're trying to do.
What is the scope of your variable? It needs to be package level, not task level.
What is written to the variable? Is it a list of IDs, or just 1 ID at a time (within some sort of loop)? You say that it is set to the correct value 'each time', which makes me think that you are looping. So by the time you get to dataflow task B, the variable has been set and reset several times and task B will only ever see the last value it was set to - assuming the scope of the variable is correct.
November 21, 2009 at 2:19 am
Let's recap...
i have a setup which looks like this
<Task A> --> ForEach(<TaskB>)
Task A produces Data which it stores in a Variable "v1" (AdoDataSet with exactly one DataTable in it) which has package level scope.
The ForEach element iterates over each row in v1 and assigns the value of column 5 within the current row of v1 to a different variable called FirstIdRunner (Full Name User::FirstIdRunner) which has ForEach Scope.
Task B, embedded within the forEach construct, uses this variable to construct sql queries. These are realized through variables with the "evaluate as expression" flag set. These reference the foreach-scope variable FirstIdRunner as shown above in the abbreviated sql query. These Variables have TaskB Scope.
So we have 3 types of variables:
* Package Scope: v1
* ForEach Scope: FirstIdRunner
* TaskB Scope: Several Variables carrying expressions which contain "FirstIdRunner"
The problem at hand now is that within the expressions within TaskB the FirstIdRunner always evaluates to zero while it has correct values at the foreach level, which i verified via breakpoints
Viewing 9 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply