none
Cannot set property in SSIS 2008 Send Mail task when executing package programmatically

    Question

  • I have a Send Mail task where I am setting three properties from a Package Configuration file.  The Package Configuration file location is a local drive.  The errors I am getting are...

    Package Error: The result of the expression "@[User::SMTPServer]" on property "SmtpServer" cannot be written to the property. The expression was evaluated, but cannot be set on the property.

    Package Error: The result of the expression "@[User::EmailFrom]" on property "FromLine" cannot be written to the property. The expression was evaluated, but cannot be set on the property.

    The result of the expression "@[User::EmailTo]" on property "ToLine" cannot be written to the property. The expression was evaluated, but cannot be set on the property.

    The package executes correctly when running it from my Visual Studio Dev environment but when I attempt to execute it programmatically it fails. 

    I am running this from SSIS 2008.  The package is stored in the MSDB but does not run on the SQL Server machine (the program that executes the package is running on another machine so my understanding is that the package itself is running from that machine).

    Any ideas?

    Friday, August 20, 2010 2:31 PM

Answers

  • First, to answer your question...all three variables are String data type.  The value of the SMTPServer variable is an IP Address and the value of the FromLine and ToLine are email addresses.

    I have been messing with the package for two days.  So that I could move on I just removed the expressions and hard-coded the FromLine and ToLine properties from the Send Mail task and also hard-coded the SmtpServer property from the SMTP Connection Manager so I could get past this error and onto the "real" work I was trying to do.

    Three hours later a DBA came to help me figure out what the problem was.  I added the information back so these three properties were set from variables to show him the error and .... drum roll please ... everything worked just fine.  We no longer have the errors occurring.

    SSIS and I have a love/hate relationship!!!

    If anyone can explain this I would love to hear some theories...other than that I am moving on to other tasks and cross my fingers that I never have to look at this package again.

    Thanks for all the input.

    Friday, August 20, 2010 7:12 PM

All replies

  • Are you setting those variables through the program that runs the package? If so, can you share the code?


    John Welch | www.varigence.com | www.agilebi.com | ssisUnit.codeplex.com
    Friday, August 20, 2010 2:36 PM
  • 2008.  The package is stored in the MSDB but does not run on the SQL Server machine (the program that executes the package is running on another machine so my understanding is that the package itself is running from that machine).

    You are correct. Does the machine that is executing the package have the Configuration File stored in the exact same path on its hard drive?

    Just curious, it looks like you are using the COnfiguration to set the value of some variables, and then using those variables in Expressions in the Send Mail Task. Why not eliminate the middle man and set the Properties of the Send Mail task directly using the Configuration? Also, instead of setting the *property* of the SMTP Server that the task references, you should be using the configuration to set the Connection string Property of the Connection Manager,

    I have a Send Mail Task in all my 'master' packages in the root node Error Handler's OnTaskFailed event. EVERY copy of this task is named "Failure Email" so that I have ONE configuration entry in my SQL Config table, and EVERY package uses it to set properties of From, To, etc.


    Todd C - MSCTS SQL Server 2005 - Please mark posts as answered where appropriate.
    Friday, August 20, 2010 2:39 PM
  • Yes the machine does have the Configuration File.  I have confirmed that it is finding the configuration file because I made the XML in the configuration file invalid and then I execute the package after making it invalid I get the following error..."Cannot load the XML configuration file. The XML configuration file may be malformed or not valid."  Which tells me the my package is able to find the configuration file.

     

    It's a good question why not set the properties directly.  I was not the original creator of the package so I am not sure why he chose to do it this way, unless it was either to standardize the setting of properties or to make it possible to use the values in multiple tasks.  Whatever the case I change it to set the properties directly and now I get the following errors...

    Process configuration failed to set the destination at the package path of "\Package\Error Handler\Send Failure Email.Properties[FromLine]". This occurs when attempting to set the destination property or variable fails. Check the destination property or variable.

    Process configuration failed to set the destination at the package path of "\Package\Error Handler\Send Failure Email.Properties[ToLine]". This occurs when attempting to set the destination property or variable fails. Check the destination property or variable.

    Failed to load at least one of the configuration entries for the package. Check configuration entries for "SMTP" and previous warnings to see descriptions of which configuration failed.

    Thanks for your help.

     

     

    Friday, August 20, 2010 3:32 PM
  • Also...here is the package configuration file.

     <?xml version="1.0"?>
    <DTSConfiguration>
     <DTSConfigurationHeading>
      <DTSConfigurationFileInfo GeneratedBy="x" GeneratedFromPackageName="x" GeneratedFromPackageID="{781DBC0E-91A7-4303-964D-4D0A9AEF57AF}" GeneratedDate="8/20/2010 10:25:14 AM"/>
     </DTSConfigurationHeading>
     <Configuration ConfiguredType="Property" Path="\Package.Connections[SMTP Connection Manager].Properties[ConnectionString]" ValueType="String">
      <ConfiguredValue>SmtpServer=172.17.190.251;UseWindowsAuthentication=False;EnableSsl=False;</ConfiguredValue>
     </Configuration>
     <Configuration ConfiguredType="Property" Path="\Package\Error Handler\Send Failure Email.Properties[FromLine]" ValueType="String">
      <ConfiguredValue>xxx.dba@xxxxxxx.com</ConfiguredValue>
     </Configuration>
     <Configuration ConfiguredType="Property" Path="\Package\Error Handler\Send Failure Email.Properties[ToLine]" ValueType="String">
      <ConfiguredValue>xxxxxxx@xxxxx.com</ConfiguredValue>
     </Configuration>
    </DTSConfiguration>

     

    Friday, August 20, 2010 3:33 PM
  • Sorry...one more thing to point out.  The package runs now because the above messages are just warnings.  But it would be interesting in knowing what these messages mean because they may clue me in to why it isn't able to set these values using variables.

    Thanks for the help.

    Friday, August 20, 2010 3:40 PM
  • Did you re-name the Send Mail task, or perhaps move it to another Event in the Event Handler?

    Remeber that the Configuration thingy is trying to navigate to the path of: \Package\Error Handler\Send Failure Email.Properties[FromLine]

    but if you re-named it to something else, or moved it, it cannot resolve the path.

    All else fails, delete the configurations and start over.


    Todd C - MSCTS SQL Server 2005 - Please mark posts as answered where appropriate.
    Friday, August 20, 2010 3:44 PM
  • I did not rename or move the Send Mail task.

    I have deleted the Send Mail task and recreated it.  I put it back to using the variables instead of setting the task properties directly (since that is the way it original was designed and it *should* work that way).  I have deleted the variables and created them.  None of this worked.

    I am now back to the original errors.

    Package Error: The result of the expression "@[User::SMTPServer]" on property "SmtpServer" cannot be written to the property. The expression was evaluated, but cannot be set on the property.

    Package Error: The result of the expression "@[User::EmailFrom]" on property "FromLine" cannot be written to the property. The expression was evaluated, but cannot be set on the property.

    The result of the expression "@[User::EmailTo]" on property "ToLine" cannot be written to the property. The expression was evaluated, but cannot be set on the property.

    These are errors so the package fails.

    Friday, August 20, 2010 4:19 PM
  • What is the value are those variables being set to? What type of variables are they?

    This error can mean that the data type for the result of the expression isn't a match to the property type.


    John Welch | www.varigence.com | www.agilebi.com | ssisUnit.codeplex.com
    Friday, August 20, 2010 4:26 PM
  • First, to answer your question...all three variables are String data type.  The value of the SMTPServer variable is an IP Address and the value of the FromLine and ToLine are email addresses.

    I have been messing with the package for two days.  So that I could move on I just removed the expressions and hard-coded the FromLine and ToLine properties from the Send Mail task and also hard-coded the SmtpServer property from the SMTP Connection Manager so I could get past this error and onto the "real" work I was trying to do.

    Three hours later a DBA came to help me figure out what the problem was.  I added the information back so these three properties were set from variables to show him the error and .... drum roll please ... everything worked just fine.  We no longer have the errors occurring.

    SSIS and I have a love/hate relationship!!!

    If anyone can explain this I would love to hear some theories...other than that I am moving on to other tasks and cross my fingers that I never have to look at this package again.

    Thanks for all the input.

    Friday, August 20, 2010 7:12 PM