|
|
|
Forum Newbie
Group: General Forum Members
Last Login: Saturday, November 21, 2009 2:53 AM
Points: 5,
Visits: 7
|
|
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?
|
|
|
|
|
SSCommitted
Group: General Forum Members
Last Login: Yesterday @ 9:46 AM
Points: 1,860,
Visits: 2,120
|
|
Where are you entering the text of the SQL query containing the variable?
|
|
|
|
|
Forum Newbie
Group: General Forum Members
Last Login: Saturday, November 21, 2009 2:53 AM
Points: 5,
Visits: 7
|
|
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
|
|
|
|
|
SSCommitted
Group: General Forum Members
Last Login: Yesterday @ 9:46 AM
Points: 1,860,
Visits: 2,120
|
|
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?
|
|
|
|
|
Forum Newbie
Group: General Forum Members
Last Login: Saturday, November 21, 2009 2:53 AM
Points: 5,
Visits: 7
|
|
Sql is executed as "SQL from a variable".
The variable is a expression and contains the variable "FirstIdRunner" which is obtained from Task "A"
|
|
|
|
|
SSCommitted
Group: General Forum Members
Last Login: Yesterday @ 9:46 AM
Points: 1,860,
Visits: 2,120
|
|
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).
|
|
|
|
|
Forum Newbie
Group: General Forum Members
Last Login: Saturday, November 21, 2009 2:53 AM
Points: 5,
Visits: 7
|
|
| well yes, this is, as i explained, exactly what I'm doing
|
|
|
|
|
SSCommitted
Group: General Forum Members
Last Login: Yesterday @ 9:46 AM
Points: 1,860,
Visits: 2,120
|
|
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.
|
|
|
|
|
Forum Newbie
Group: General Forum Members
Last Login: Saturday, November 21, 2009 2:53 AM
Points: 5,
Visits: 7
|
|
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
|
|
|
|
