Expressions not always evaluated in correct order

Hello!

I have an SSIS package, run by the DTExec utility, each night. When I run my package, I assign a value to a variable (package scope) (/SET "\Package.Variables[User:: psRunNo]";0154). (Note: all the variables here are strings).

Value for variable psRunNo = 0154

In the package, I have another package variable called S_SOURCE_FILE_NAME, which is an expression and that uses the variable psRunNo.

Expression for S_SOURCE_FILE_NAME = @.[User:: psRunNo] + "_{TABLENAME}.txt"

So, at the beginning, the value of variable S_SOURCE_FILE_NAME is 0154_{TABLENAME}.txt with that example.

Then, I use the variable S_SOURCE_FILE_NAME in the expression used to assign the ConnectionString property of a Connection (a source file), like this:

REPLACE( @.[User:: S_REJECTED_ROWS_FILE_NAME] ,"{TABLENAME}", "STADDRES")

So here for example, the final value for ConnectionString would be 0154_STADDRES.txt

Everything works nice, most of the time. Sometimes (intermittent problem), the value of the ConnectionString for one of the many Connections I have in the package is not assigned with the right value of psRunNo. The ConnectionString gets the value of psRunNo which is saved into the package (when it was deployed) instead of the value of psRunNo passed with the DTExec.

It is like if the ConnectionString value was computed before the variable psRunNo (and S_SOURCE_FILE_NAME expression) was assigned to the new value, but only for one of the connections (all my connections use the same kind of expression for their ConnectionString property).

Does somebody had similar precedence problem? Is there some settings I could use to indicate a precedence in assignation of variables ? Where does the "natural" precedence in assignation comes from in SSIS ?

Thanks!

Isabelle_ wrote:

Does somebody had similar precedence problem? Is there some settings I could use to indicate a precedence in assignation of variables ? Where does the "natural" precedence in assignation comes from in SSIS ?

Thanks!

There isn't really a precedence. If VariableC depends on VariableB which depends on VariableA then all will get evaluated when VariableC gets used somewhere. The expressions are not pre-evaluated or anything like that.

This doesn't help explain the problem you are seeing. I confess I can't undersand why it might be happening. Can you post a repro?

-Jamie

|||

I could not post a reproduction as is, because even if I would make up a small package with my case, this case is intermittent, so almost impossible to get it right... I was thinking maybe on making a script at the beginning of my package and "manually" assign the variable values, instead of using expressions.. Normally, the outcome would be the same, but maybe that would ensure that those variables always get their right values before being use... ?

|||

Isabelle_ wrote:

I could not post a reproduction as is, because even if I would make up a small package with my case, this case is intermittent, so almost impossible to get it right... I was thinking maybe on making a script at the beginning of my package and "manually" assign the variable values, instead of using expressions.. Normally, the outcome would be the same, but maybe that would ensure that those variables always get their right values before being use... ?

You could try that but this is just papering over the cracks. Something else is wrong here. Could it be that you haven't got EvaluateAsExpression=TRUE?

-Jamie

|||

I've check, and I got the EvaluateAsExpression=TRUE. I guess also that if I wouldn't have it to true, it would never work. But it works, 90% of the time ...

Ok, thanks anyway. I've changed my thing so I just have one level of expression. I will let it run for few days/weeks and see if this problem still happen.

Thanks everybody for your input!