May 6, 2009 at 8:21 am
I have resubmitted the article with images to contribution center.How will it be updated?
May 6, 2009 at 8:21 am
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
May 6, 2009 at 8:50 am
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 🙂
May 6, 2009 at 9:01 am
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.
May 6, 2009 at 9:01 am
No images - check what you post.
May 6, 2009 at 9:06 am
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!
May 6, 2009 at 9:08 am
I can't see the images ... anyway, the article seems to be very interesting (please, repost the images). Thanks. Mary.
May 6, 2009 at 9:16 am
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
May 6, 2009 at 10:23 am
Images are available now. Thanks!!!
May 6, 2009 at 10:46 am
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
May 6, 2009 at 11:09 am
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.
May 6, 2009 at 11:54 am
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.
May 7, 2009 at 7:51 am
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.
May 7, 2009 at 9:32 am
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.
May 7, 2009 at 9:50 am
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 41 total)
You must be logged in to reply to this topic. Login to reply