Results 1 to 2 of 2
  1. #1
    Join Date
    May 2002
    Posts
    8

    Unanswered: SSIS package loses MessageSource in Send Email Task

    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

  2. #2
    Join Date
    May 2002
    Posts
    8
    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/...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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •