November 18, 2016 at 2:33 pm
I'm modifying an SSIS package to send email if there is a validation error in the data.
I have everything all set up, including a package variable that contains the validation error message (User::ValidationErrorMessage) ... in the Send Email Task, I set the from, to their values. I set the MessageSourceType to Variable ... I then set the MessageSource (using the dropdown) to User::ValidationErrorMessage ... This variable then gets its value set in a C# script task just before the send email task.
If I then click OK, and then re-open the editor for the task, the MessageSource is empty. When I run the package, it tells me that there was an exception on the task, with the error message:
[Send Mail Task] Error: An error occurred with the following error message: "Failed to lock variable "There was an error while processing the imported data. The file imported contained one or more validation errors.
* The file contained ...{snip}... Expected: 0; Found: 2.
" for read access with error 0xC0010001 "The variable cannot be found. This occurs when an attempt is made to retrieve a variable from the Variables collection on a container during execution of the package, and the variable is not there. The variable name may have changed or the variable is not being created.".
System.Runtime.InteropServices.COMException: Failed to lock variable "There was an error while processing the imported data. The file imported contained one or more validation errors.
* The file contained ...{snip}... Expected: 0; Found: 2.
".
The text it's quoting as being unreachable is the CONTENTS of the variable it should be getting... but it's not the name of it. What gives?
In the script task, this is how I set the variable:
[highlight=c#]
Dts.Variables["User::ValidationErrorMessage"].Value = string.Format("There was an error while processing the imported data. The file ({0}) imported contained one or more validation errors.{1}{2}", fileName, Environment.NewLine, errorMessage.ToString());
[/highlight]
As far as I can tell, I'm setting it right... so why does it "forget" the variable I set it to?
NOTE: If I do not set the variable in the C# script, and enter a default value in the variables list, then I still get the same error, except then it uses the default text as the variable...
-tg
November 19, 2016 at 2:39 pm
Did you add variable 'User::ValidationErrorMessage' to the list of ReadWrite variables in the Script Task's properties?
November 20, 2016 at 7:58 am
Quick thought, how about changing the logic to logging all errors into a table by i.e. a batch ID and after the package has run then run another task that sends an email if anything exists there for the batch? Makes handling the error reporting much simpler IMHO
😎
November 21, 2016 at 7:42 am
Phil Parkin (11/19/2016)
Did you add variable 'User::ValidationErrorMessage' to the list of ReadWrite variables in the Script Task's properties?
Yup... because there were a number of things being passed in and out of the script task that was the first thing I checked. The second thing I checked was spelling. All seemed to be fine.
Eirikur Eiriksson (11/20/2016)
Quick thought, how about changing the logic to logging all errors into a table by i.e. a batch ID and after the package has run then run another task that sends an email if anything exists there for the batch? Makes handling the error reporting much simpler IMHO😎
Agreed. this is an existing process, and I'm simply updating a few things on the front-end (beginning of the process, not the UI) of it. Keeping track of the validation errors isn't the problem, it's simply sending the email.
On that note... I found out where things went wrong... apparently somewhere I had added User::ValidationErrorMessage as an expression and set it to the MessageSource ... I have no idea how the heck I did that, but somehow I managed to. That explains why it was trying to use the text of the variable as the variable name. In the meantime I also found this script: http://www.codeproject.com/Articles/85172/Send-Email-from-SSIS-with-option-to-indicate-Email that gives finer control over the mail authentication and how the mail can be sent out (one of the requirements of the project is that the email configuration be as confogurable as possible.
And I'm now getting my emails as expected. Woo woo!
-tg
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply