September 9, 2011 at 12:45 pm
Hello,
Not sure how to solve this issue, so I'm hoping you all can help.
I currently have a Variable, named "SqlCommand" which has its "EvaluateAsExpression" property set to TRUE. I have several other variables which are being referenced in the expression, which are either statically set dynamically set in the package. All variables are package-level scope variables.
When I run the package, the first 3 tasks set 3 different variables which are used in the @SqlCommand expression. I have confirmed this is indeed happening. The next task of the package is a Data Flow task which starts out with an OLE DB Source. The source is set to "SQL command from variable" and uses the @SqlCommand variable. My query was not producing the intended results, so I've checked the actual @SqlCommand value here and it does not contain the updated values of the variables. Any ideas?
Thanks in advance for your assistance.
September 14, 2011 at 3:32 pm
Just wanted to post back to let folks know what the resolution was for this.
As it turns out, the expression evaluation was resulting in a string > 4000 characters, which I had known previously that SSIS isn't too happy about. I resolved the issue by building the string variable in a Script Task, rather than evaluating it as an expression and now everything works as intended.
Interestingly enough, the package was completing with success on my machine. After grappling with the issue for a while, I decided to deploy it to one of our Dev servers to see if the change in environment made any differences (I didn't expect it would). Lo and behold, the package failed executing with the error message of "expression evaluation resulting in a string > 4000 characters" (paraphrased for brevity...).
So, I went back to my local copy and checked the execution results. Though the package was completing with success on my box, the execution results actually contained the 4000 character error in the log. On my machine, that wasn't enough to result in a failure, but on the dev server, it was enough to result in a failure.
Anyway, hope that helps someone down the line. If the data isn't returning as expected, even though your package is executing with success, check the execution results for any abnormalities... Probably doesn't hurt to check regardless. I know I will be better about that from now on.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply