I tried to use the "ForcedExecutionValue" on several Send Mail Tasks and log the execution into a ExecValueVariable so that at the end of the package I can log into a table to say whether the data check is successful or not (by determine whether an email was sent out)
I set up a Boolean variable that is accessible at the package level, then set up my Send Mail Task as the screenshot below with Boolean as my ForcedExecutionValueType. When I run the package, I got the error described below.
Just to make sure this is not another issue of SSIS having with Boolean type ( you also can't set variable value from xp_cmdshell of type Boolean), I used variables of types String, Int32, DateTime with the corresponding ForcedExecutionValueType. The only way to get around this error, was to set my variable to type Object, but then when you try to get the value out later, the Object is null.
I didn't spend enough time on this to see whether it's really a bug in SSIS or not, or is this just how Send Mail Task works. Just want to log the error and will circle back on this later to narrow down the issue some more. In the meantime, please share if you have run into the same problem. The current workaround is to attach a script task at the end.
Also, need to note 2 existing limitation:
Data check needs to be done serially because every check needs to be inner join to a master table. The master table has all the data in a single XML column and hence need to be retrieved with XQuery (a fundamental design flaw that needs to be changed)
The next iteration will be to change this design into a FOR loop and pull out the checking query from a table somewhere with all the info needed for email task, but is being put to the back of the priority.
Error Message:
Error: 0xC001F009 at CountCheckBetweenODSAndCleanSchema: The type of the value being assigned to variable "User::WasErrorEmailEverSent" differs from the current variable type. Variables may not change type during execution. Variable types are strict, except for variables of type Object.
Error: 0xC0019001 at Send Mail Task on count mismatch: The wrapper was unable to set the value of the variable specified in the ExecutionValueVariable property.
Screenshot of my Send Mail Task setup:
