Sending Mail In SSIS Using Script Task- Simpler and Flexible Approach

  • Manish Sinha

    Ten Centuries

    Points: 1023

    I have resubmitted the article with images to contribution center.How will it be updated?

  • Mathew Michuta

    Old Hand

    Points: 330

    I disagree that it is easier to send mail from inside a script task.

    I use a script task to set addresses, conditionally attach multiple files, modify the content of the email, etc, then use expressions inside the send mail task. This makes it much easier to see the program flow at a glance, and ADD to the email from multiple places within your package before being sent.

    An error from a send mail task is much easier to decipher than an error inside a script task, too!:-D

  • Manish Sinha

    Ten Centuries

    Points: 1023

    The article mentions that using script task is simpler if the required mail functionality is complex.

    Example: I need to send to 100 recipients one by one with different messages and subjects. I also need to store in database or create a text file having the datetime and email addresses for all the mails sent.This is small example.You can do number of things using script task.

    I do not say it's always simpler..........It depends on functionality you need 🙂

  • Brett Flippin

    Right there with Babe

    Points: 717

    It sounds like from that example that you could do that with For or Foreach Loop container to loop through your emails and do the appropriate operations instead of maybe embedding that logic in a script task.

    This is the beauty of SSIS, Microsoft has provided us a rich tool to do work the way we want to.

    In my opinion and personal best practice, a script task is a last resort to add functionality that isn't already present in SSIS. They aren't as maintainable in the long run, and they obfuscate their true function in the package by being able to execute any number of operations that maybe aren't detailed in the task's name or some comments.

  • castspeller

    SSC Rookie

    Points: 31

    No images - check what you post.

  • Lempster

    SSCoach

    Points: 15636

    Brett Flippin (5/6/2009)


    It sounds like from that example that you could do that with For or Foreach Loop container to loop through your emails and do the appropriate operations instead of maybe embedding that logic in a script task.

    This is the beauty of SSIS, Microsoft has provided us a rich tool to do work the way we want to.

    In my opinion and personal best practice, a script task is a last resort to add functionality that isn't already present in SSIS. They aren't as maintainable in the long run, and they obfuscate their true function in the package by being able to execute any number of operations that maybe aren't detailed in the task's name or some comments.

    Hear hear!

  • memjm2003

    SSC Rookie

    Points: 44

    I can't see the images ... anyway, the article seems to be very interesting (please, repost the images). Thanks. Mary.

  • Peter Rijs

    Hall of Fame

    Points: 3400

    Brett Flippin (5/6/2009)


    It sounds like from that example that you could do that with For or Foreach Loop container to loop through your emails and do the appropriate operations instead of maybe embedding that logic in a script task.

    This is the beauty of SSIS, Microsoft has provided us a rich tool to do work the way we want to.

    In my opinion and personal best practice, a script task is a last resort to add functionality that isn't already present in SSIS. They aren't as maintainable in the long run, and they obfuscate their true function in the package by being able to execute any number of operations that maybe aren't detailed in the task's name or some comments.

    That's my position as well.

    So Manish, why don't you post the requirements and the "ScriptTask solution" for your more complex example? I'm sure there will be quite a lot of people here willing to post the appropiate "SendMail Task solution" to compare it with.

    Peter Rijs
    BI Consultant, The Netherlands

  • Manish Sinha

    Ten Centuries

    Points: 1023

    Images are available now. Thanks!!!

  • Bk-384756

    SSC Veteran

    Points: 221

    Thank you for the article, Manish!

    Question on your script code...

    What is the difference between using "User::AmountDue" in Dts.Variables(), and

    just using "MailSubject" ?

    I struggled mightily when starting my SSIS scripting adventures because the use of "User::" in the variable string seems to be arbitrary. What is the logic? I have not seen any doc on this distinction.

    Thanks again!

    Bk

  • Lempster

    SSCoach

    Points: 15636

    If you are going to use a Script Task (and I wouldn't personally for reasons already stated in previous posts), you should use the concepts LockForWrite/Read and LockOneForWrite/Read rather than list the variables. This prevents them being locked for longer than necessary.

    Can't say I understand it fully (which is why I steer clear of script tasks/components if possible ;-)), but that's what I was told by a 'guru' on an SSIS 'Deep Dive' course.

  • Ken-855952

    Valued Member

    Points: 50

    This example can be done pretty simply using expressions for the Send Email task.

    Instead of all this scripting, simply set the Subject expression to "Status for Order: " + @[User::str_OrderNum] and the Body to "The order number: " + @[User::str_OrderNum] + " placed on " + @[User::str_OrderDate] + " is ready for shipment. Please generate invoice for amount: " + @[User::str_AmountDue]. Now this approach might be more suited for some complex querying with loops, e.g., Multiple orders or order with line item detail, but even then I would use another approach. You should be using expressions for email tasks anyway, as I HIGHLY recommend using configurable package variables that become expressions for the To and CC line of ANY email function.

    For very complex emails I would highly recommend creating a stored procedure that returned a nicely formatted HTML string to be the message body. Especially for internal corporate operations where evryone is viewing the email with the same tools. Two side benefits of this are you can modify either the content or presentation of the data without cracking open the SSIS package, and you can replicate the email for static data simply by calling the procedure, e.g., Payments made on a certain day or hours worked at a location by an employee.

    If someone wants me to put together a demonstration of that I can. Additionally there may be a nice graceful solution using XML and XSLT, since XML is an output format for 2005 and beyond, however HTML must be hand crafted with embedded HTML formatting commands.

    Honestly, I think script based email should be a VERY last resort. Expressions, albeit a bit awkward for lots of information, are a better approach to dynamic data. Not to mention having to store the binary code for any script in the dtsx package which is a hog (Use notepad to look at a script laden dtsx file sometime).

    As for locking variables, I do prefer Manish's approach of using the ReadOnly and ReadWriteVariable declarations in the script window as opposed to locking functions in the script. It is more evident what variables the script is using, less things can go awry, and unless you have very long running scrips AND (not OR) concurrent process that use them, it isn't necessary so why assume that unnecessary burden. Sure they may be locked for 20ms more than necessary, but what other process is using them? They are scoped at the package instance level so the only contention is inside your own package instance.

  • Lempster

    SSCoach

    Points: 15636

    Ken (5/6/2009)


    ....unless you have very long running scrips AND (not OR) concurrent process that use them.....

    Precisely. If you want to design your packages to be able to take advantages of the threading capabilities of multiple CPU servers so that tasks are executed in parallel/concurrently, then variable locking can become a problem. An alternative would be to scope variables at the individual Data Flow levels, but you would need to define more variables and passing data between them would be very difficult if not impossible.

  • Savagegarde46

    Newbie

    Points: 5

    I liked the article but I had a project that I just created and is running with no issues and i also needed the subject and the message to be dynamic and what i did was used the send mail task but created expresions that used the variables which held the dynamic data. So there is alot of ways that you can create dynamic mail in SSIS but its all a persons opinion what is simpler.

  • BCC-493036

    Mr or Mrs. 500

    Points: 525

    Could you give me some hints how to set up dynamic email subject and message? Thanks.

    --------------------------------------------------------------
    DBA or SQL Programmer? Who Knows. :unsure:

Viewing 15 posts - 16 through 30 (of 42 total)

You must be logged in to reply to this topic. Login to reply