none
SSIS Send Mail task problem

    Question

  • Hi,

    I have got the problem connected with Send Mail Task in SSIS.
    The general idea was: sending email alert to different email users. I tried to use Foreach Loop task which loops through dataset (where the emails are stored) and assign different email addresses to SSIS package variable.

    When I try to use this task inside Foreach Loop task I receive this error message: "Error: 0xC002F304 at Send Mail Task, Send Mail Task: An error occurred with the following error message: "The parameter 'address' cannot be an empty string."

    The SSIS package has variable (string) which is binded with ToLine - property of Send Mail task (it was set up in Expressions tab of Send Mail task). This ToLine property should assign different emails during every loop of Foreach Loop task.

    But unfortunately is seems to be not true, because Send Mail task does not allow to assign different values to ToLine property while is in Run-Time.

    Have you discovered this quite bizzare Send Mail task behaviour before ??
    I appreciate every help and suggestions.

    Thanks,Marek

    Tuesday, June 06, 2006 10:22 AM

Answers

  • I just tried to create a small test package, and it works fine. Here are the steps I've followed:

    1) Create variable MailAddress at package level

    2) Drop ForEach loop, inside it Send Mail Task

    3) Double click the loop shape, select For Each Item enumerator

    4) Add a column and several e-mail addresses as values

    5) Switch to Variable Mapping, map the MailAddress variable to iterator index 0

    6) Close the For Each loop editor

    7) Open Send Mail Task, configure connection manager, fill From, Subject and MessageSource properties,

    8) In expressions map ToLine to @[User::MailAddress]

    9) close Mail Task editor

    10) In property panel set Delay Validation to true

    11) Run the package, verify I've received the e-mails.

    --

    Alternatively, I've tried removing step 5 and instead setting the variable value in a script task inside the same loop, connected to the Send Mail Task with precedence constraint.

    Both versions work fine, I don't see any bug here. Please check that you've followed the steps as suggested, I suspect you've missed something.

    Friday, June 09, 2006 1:33 AM

All replies

  • Hi,

     

    Any idea how to solve this problem ?? I got stuck with this issue.

     

    Your help is very much appreciated.

    Marek

     

     

    Wednesday, June 07, 2006 10:58 AM
  • check if variable property "Evaluate as expression" is true. Maybe the variable is not correctly resolved at runtime. You can check this by debugging the package and setting a breakpoint before the execution of the task.

     

    Nobsay

    Wednesday, June 07, 2006 12:52 PM
  • Hi Nobsay,

     

    I checked your suggestion. It does not work, my variable does not contain expression - it only receives different email addresses (as string - data type) from SQL table (which is sent to object variable in Foreach Loop task). But I changed this property according to your idea.

    Unfortunately it is not working so far.

    It drives me crazy.....

    MarekR

     

    Wednesday, June 07, 2006 1:36 PM
  • What is the scope of the variable that you are assigning the e-mail address? Have you added the expression to the e-mail tasks so that it uses the variable?

    I am not having any trouble with this at all (except the bug I noted earlier with SmtpServer).

    Wednesday, June 07, 2006 2:15 PM
  • Hi FredH,

    The scope of the variable covers the IS package scope.

    I have such expression in Send Mail task:

    ToLine - @[User::Script_AssignEmail]

    This variable (Script_AssignEmail) stores different email address data in every loop step. It should pass different information to ToLine property of Send Mail task, but I still receive such error:

    Error: 0xC002F304 at Send Mail Task, Send Mail Task: An error occurred with the following error message: "The parameter 'address' cannot be an empty string.

    Marek

    Wednesday, June 07, 2006 2:25 PM
  • The problem might be with the variables not being populated at run time, you can try setting the DelayValidation to true in the properties window.

    Wednesday, June 07, 2006 4:54 PM
  • DelayValidation will only push the error from Design-Time to Run-Time but that does not appear to be the issue (I am assuming this is a Run-Time error).

    You should set a breakpoint on the e-mail task so you can look at the variables. It really looks like a script is not setting the variable correctly.

    Wednesday, June 07, 2006 5:30 PM
  • I think setting DelayValidation to true is the correct approach here - usually the task is validated upfront, so any error is caught fast. But if the task properties depend on execution of other tasks, you need to delay validation until this data is available, when the property can be evaluated to expected value.
    Thursday, June 08, 2006 4:44 AM
  • Hi Michael,

    Maybe setting DelayValidation property is the correct approach, but it does not work. I changed every task DelayValidation property to True but I still receive error :

    Error: 0xC002F304 at Send Mail Task, Send Mail Task: An error occurred with the following error message: "The parameter 'address' cannot be an empty string.

    Any ideas ? I think this is BUG!!!!!!!!!!!!!!!!!

    We need help from Microsoft, some guys from this company should test this and take it into consideration regarding hotfixes and further service packs.

     

    Cheers, Marek

     

    Thursday, June 08, 2006 9:03 AM
  • Can you setup a really simple test package with the failing task? You can send it to my hotmail account just use my fred hirschfeld separated by an underscore account there.
    Thursday, June 08, 2006 3:01 PM
  • I just tried to create a small test package, and it works fine. Here are the steps I've followed:

    1) Create variable MailAddress at package level

    2) Drop ForEach loop, inside it Send Mail Task

    3) Double click the loop shape, select For Each Item enumerator

    4) Add a column and several e-mail addresses as values

    5) Switch to Variable Mapping, map the MailAddress variable to iterator index 0

    6) Close the For Each loop editor

    7) Open Send Mail Task, configure connection manager, fill From, Subject and MessageSource properties,

    8) In expressions map ToLine to @[User::MailAddress]

    9) close Mail Task editor

    10) In property panel set Delay Validation to true

    11) Run the package, verify I've received the e-mails.

    --

    Alternatively, I've tried removing step 5 and instead setting the variable value in a script task inside the same loop, connected to the Send Mail Task with precedence constraint.

    Both versions work fine, I don't see any bug here. Please check that you've followed the steps as suggested, I suspect you've missed something.

    Friday, June 09, 2006 1:33 AM
  • Hi Michael,

    Thank you for valuable help.

    I checked my package yesterday. It turned out that the problem was in Execute SQL task that was preparing DataSet with emails - I used SQL Case statement syntax which (some how) casued Send Mail task failure. When I replaced Case statement in SELECT query with simple column name - my package started working.

    I have to investigate it more deeply so thank you all guys for valuable support.

    May the SSIS force be with you!

    All the best, Marek

     

    Friday, June 09, 2006 8:30 AM
  • I'm trying to do this by pulling a value from a table in my database but it's not working.  I have a execute sql task that gets the value and assigns it to a result set variable I have setup.  The field is a varchar and the variable is a string.  I keep getting these errors below but I have other variables setup the same way that I can pull from this table with no problems?


    Error: A deadlock was detected while trying to lock variable "User::ErrorEmail" for read access. A lock could not be acquired after 16 attempts and timed out.

    [Send Mail Task] Error: No recipient is specified.

    Error: The expression "@[User::ErrorEmail]" on property "ToLine" cannot be evaluated. Modify the expression to be valid.

    Error: The type of the value being assigned to variable "User::ErrorEmail" differs from the current variable type. Variables may not change type during execution. Variable types are strict, except for variables of type Object.
    Mike
    Friday, February 20, 2009 2:32 PM
  • have u been able to resolve this Mr?
    dsu
    Friday, May 08, 2009 4:19 AM
  • Yes I did.  you have to click on the properties of the task and set the delay validation property to true.  I forgot to mark it as answered.
    Mike
    • Proposed as answer by mr4100 Friday, May 08, 2009 11:37 AM
    Friday, May 08, 2009 11:37 AM
  • I did but still missing something ,
    Can you please explain step by step how you did that.
    That will be helpful for others as well

    Thank you and waiting
    dsu
    Friday, May 08, 2009 1:33 PM
  • 1. set up a variable as string type.

    2. drop an execute sql task down and write your query to pull the field you need from the table and then pull it back to your variable as a result set.  Use single row in result set.  

    3. drop your send mail task down and in the expressions tab set the Toline to your variable and make sure the Delayvalidation in properties is set to True.


    Mike
    • Proposed as answer by DSU007 Friday, May 08, 2009 3:47 PM
    Friday, May 08, 2009 2:15 PM
  • Ok, Good news is taht I have resolve this, however, it took little time But
    the problem was lying on defining varaibles i had to delete that package and re-do that.

    Thanks

    dsu
    Friday, May 08, 2009 3:44 PM
  • Delete the package or variable?  Glad you got it working.
    Mike
    Friday, May 08, 2009 4:39 PM
  • I had the same issue.  It turned out that the when I provided the eMail addresses in the ToLine, I had a semicolon after the last eMail address.  This was causing the internal functionality of the Send Mail Task to think there was an extra eMail address. 

    Once I removed the semicolon, it worked perfectly.


    I hope this helps.


    Andrew
    • Proposed as answer by rpxxxiv1 Wednesday, June 03, 2009 5:41 PM
    Wednesday, June 03, 2009 5:40 PM
  • Thanks Andrew.  I also had a semi-colon after the one e-mail address in the To line which was causing the failure. 
    • Proposed as answer by Liyasker Friday, March 12, 2010 1:55 PM
    Tuesday, September 01, 2009 9:15 AM
  • thanks .  I also had a semi-colon after the one e-mail address in the To line which was causing the failure. 

    Tuesday, February 28, 2012 4:22 PM
  • A semi-colon at the end of email distrubution List either in "To" or "CC" Address causes the SSIS evaluation as empty, as it expects another address. The last email address should not have semi-colon at the end.

    Friday, March 09, 2012 1:59 PM