Setting Variable for SSIS Pkg in SS Agent Job but to no effect
-
I have a package that creates two files, one .xls and one .csv. These files are attached to an e-mail sent once everything has completed successfully. Between the last Data Flow Task and the Send Mail Task I have set the constraint to be Constraint and Expression. The Constraint is set for Success and the Expression is @sendmail == True in order for the e-mails to be sent.
The Variable has Package Scope and is set to Boolean and Value is True. I want to run the Package via a Sql Server Agent Job which I have configured to run with the Variable set in the "Set Values" tab of the Job. I set the job to run with "Property Path" \Package.Variables[sendemail].Value and Value "False" This should have caused the Expression @sendmail == False and therfore I should not have gotten an e-mail. Unfortunately no matter what I set the variable sendemail outside of the package it retains it's original value.
I tried setting the Variable within the package to default to "False" and then set the variable sendmail in the Job to "True" and same thing the Variable remains false and no e-mail is sent.
Not sure if I am not setting the variable up correctly or not passing in a value correctly. Any help would be appreciated, I have looked for documentation and examples but have found very little information that addresses this issue...
Thanks Reg
-
I'm assuming the variable is scoped to the package? Are you using the SSIS package job step to set the variable? Have you tried the /SET option with DTEXEC.EXE to pass the parameters? http://wiki.sqlis.com/default.aspx/SQLISWiki/ScheduledPackages.html
Tommy
Tommy
Follow @sqlscribe -
The variable was set to have scope on the entire package. When I ran the job it does not fail therefore the View History did not give me any detail on the value of the Variable sendemail. After looking at the Link you suggested I went back to the SSIS Designer and selected the Package Explorer and then expanded the Variables. I brought up the Properties for the variable sendemail which I had created earlier. I noticed that that it came up as User::sendemail...When I looked at the properties the "EvaluateAsExpression" was set to False. I changed this to true and now everything works as advertized.
Where I went wrong was to assume that since I used the variable within an expression it would be evaluated as such. Now I know when I create a variable to look at the properties to make sure everything is set properly...
Thanks for the Link as that is what led me to look at the Package Explorer which I had not noticed before...
-
NP
Tommy
Follow @sqlscribe
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply