CODEWITHSUNDEEP
sqlvariablesssis
Gidil
Gidil

Reputation: 4137

SSIS variable defined by other variables not updating

In my SSIS package I have a variable (called SQL) that holds a dynamically created query.
The value of this query is defined by 2 other variables at run time.

For a reason that I have not been able to figure out, the variables value is not changing during run time :-(.
I used breakpoints and watches to check the exact value if the SQL variable and the 2 defining variables. What I see is that the 2 variables have the values I expect but the SQL variable keeps it's original version (and ends up running that way too).

This is the definition of the SQL variable:

"select *
from tbl1
where Date_Created>= to_date('"+
reverse(right(reverse(
(DT_WSTR, 20 )DATEADD( "Month", -3, (DT_DATE)@[User::StartDate])),10))
 +"', 'YYYY-MM-DD')
and 
Date_Created <=  to_date('"+
reverse(right(reverse(
(DT_WSTR, 20 )DATEADD( "Month", 3, (DT_DATE)@[User::EndDate])),10))
 +"', 'YYYY-MM-DD')
"

Any ideas?

Upvotes: 2

Views: 2684

Answers (1)

jessieloo
jessieloo

Reputation: 1872

I was having this problem and it ended up being a type cast issue in my expression.

User variable "tableName" is type String. User variable "myID" is type Int32. User variable "theQry" is type String. EvaluateAsExpression = true and this was my expression:

"select *
from "+ (DT_STR, 5,1252) @[User::tableName] +
"WHERE id = "+ (DT_STR, 5,1252) @[User::myID]

I could see in the Watch panel that the value of "tableName" and "myID" were updating but "theQry" never did.

As soon as I removed the cast function on user variable "tableName", "theQry" began updating.

"select * from "+ @[User::tableName] + "WHERE id = "+ (DT_STR, 5,1252) @[User::myID]

Upvotes: 1

Related Questions