locked
Integration Services Send Mail Task RRS feed

  • Question

  • I need to be able to change the properties on the Send Mail Task on the fly. 

    The requirement is that as the DTS Transformation executes, I need to be able to alert various users as to the completion of the DTS package. For SQL 2000 I could modify the properties of the mail task and send the appropriate e-mail message to the correct user, using an input table of users. 

    How do I accomplish the same thing in SQL 2005 Integration services? I have the transformation task working. I have created a SCRIPT TASK w the logic to build the e-mail message, but I cannot for some reason update the properties on the send mail task for the user. (Not getting the code syntactically correct, so it will not compile). Therfore, I am unable to send the an e-mail to the correct user.  
    It seems to me that I should be able to the same thing I could do w Active X in SQL 2000.

    The only example I have found so far, creates the Task in the DTS. I do not need to do this, I believe.

    Very Dense today. Can't seem to get it to come together. If at all possible, I need a good example. Please.
    Thursday, February 5, 2009 9:57 PM

Answers

  • Hi,

    Have a read of this article http://blogs.conchango.com/jamiethomson/archive/2006/07/03/SSIS_3A00_-Sending-SMTP-mail-from-the-Script-Task.aspx 

    Cheers
    Jeff
    SQL Server MVP / WARDY IT Solutions, Solutions Architect
    • Marked as answer by Tony Tang_YJ Thursday, February 19, 2009 9:46 AM
    Thursday, February 5, 2009 10:33 PM
  • To add a little more to this - one of the fundamental differences between DTS and SSIS is that SSIS packages can't modify themselves. Instead, if you want to dynamically alter properties of the package at run time, you'd use expressions.

    That being said, the reference Jeff provided is probably the easiest solution on this particular problem.
    John Welch | www.mariner-usa.com | www.agilebi.com
    • Edited by jwelch Friday, February 6, 2009 12:55 AM Corrected spelling
    • Marked as answer by Tony Tang_YJ Thursday, February 19, 2009 9:45 AM
    Friday, February 6, 2009 12:53 AM

All replies

  • Hi,

    Have a read of this article http://blogs.conchango.com/jamiethomson/archive/2006/07/03/SSIS_3A00_-Sending-SMTP-mail-from-the-Script-Task.aspx 

    Cheers
    Jeff
    SQL Server MVP / WARDY IT Solutions, Solutions Architect
    • Marked as answer by Tony Tang_YJ Thursday, February 19, 2009 9:46 AM
    Thursday, February 5, 2009 10:33 PM
  • To add a little more to this - one of the fundamental differences between DTS and SSIS is that SSIS packages can't modify themselves. Instead, if you want to dynamically alter properties of the package at run time, you'd use expressions.

    That being said, the reference Jeff provided is probably the easiest solution on this particular problem.
    John Welch | www.mariner-usa.com | www.agilebi.com
    • Edited by jwelch Friday, February 6, 2009 12:55 AM Corrected spelling
    • Marked as answer by Tony Tang_YJ Thursday, February 19, 2009 9:45 AM
    Friday, February 6, 2009 12:53 AM