SQL 2005 SSIS send mail

[doug]

I have an existing DTS package built in SQL 2000 that has a send mail task
dependent on a global variable value. If the value is > x, the email is sent
and this was defined in an ActiveX script in the workflow properties of the
send mail task. I have converted this to a SSIS package in SQL 2005. What
happened to my workflow and how do i set the global variable value to only
send emails when the value reaches this threshold?

Thanks in advance.

[Todd C]

Hello Doug:
Assuming you have totaly redesigned your DTS package as SSIS (and are not
simply trying to execute it inside of SSIS), then try this:

After you add your Send Mail task to the Control Flow and connect to it from
some other task, right-click on the green connecting arrow and select Edit.
In the resulting dialog box you can specify that the down-stream task will
execute based on a Constraint (Success or Failure), an Expression
([User::MyVariable] >= x ) or both.

Note of caution: Some people put Send Mail tasks on their Control Flow and
connect every single other task to it with a Failure constraint. The idea is
that if any taks fails, they want an email to go out. While this *sounds*
like a good idea, it won't work because the Send Mail task would only execute
if EVERY task fails. Better to use the Events tab and create an event handler
for the OnTaskFailed event of the root package.

HTH
--
=====
Todd C

Post by doug
I have an existing DTS package built in SQL 2000 that has a send mail task
dependent on a global variable value. If the value is > x, the email is sent
and this was defined in an ActiveX script in the workflow properties of the
send mail task. I have converted this to a SSIS package in SQL 2005. What
happened to my workflow and how do i set the global variable value to only
send emails when the value reaches this threshold?
Thanks in advance.

[doug]

Thank you.

Here's another question. I have another package that I converted. This one
has 95 email tasks all related to different variables and each to a different
group of recipients if the variable conditions are met. I understand how to
base the email send decision now, but when I converted it I got 95 connection
managers for email. As i clean up the converted package can 1 connection
manager be used for email?

Post by Todd C
Assuming you have totaly redesigned your DTS package as SSIS (and are not
After you add your Send Mail task to the Control Flow and connect to it from
some other task, right-click on the green connecting arrow and select Edit.
In the resulting dialog box you can specify that the down-stream task will
execute based on a Constraint (Success or Failure), an Expression
([User::MyVariable] >= x ) or both.
Note of caution: Some people put Send Mail tasks on their Control Flow and
connect every single other task to it with a Failure constraint. The idea is
that if any taks fails, they want an email to go out. While this *sounds*
like a good idea, it won't work because the Send Mail task would only execute
if EVERY task fails. Better to use the Events tab and create an event handler
for the OnTaskFailed event of the root package.
HTH
--
=====
Todd C

Post by doug
I have an existing DTS package built in SQL 2000 that has a send mail task
dependent on a global variable value. If the value is > x, the email is sent
and this was defined in an ActiveX script in the workflow properties of the
send mail task. I have converted this to a SSIS package in SQL 2005. What
happened to my workflow and how do i set the global variable value to only
send emails when the value reaches this threshold?
Thanks in advance.

[Todd C]

Doug:
Yes, you can consolidate all your Send Mail tasks to use one SMTP Connection
Manager.

You could do it manually via BIDS development environment, in which case you
would need to do it 94 times, and manually delete 94 unused SMTP Connection
Manager.

If you are creative with editing XML via script, you could edit the .dtsx
file outside of SSIS Designer (make a copy first, though!) and loop through
SMTP Connection Managers 2 through 95 and change them to match the first.

HTH
=====
Todd C

Post by doug
Thank you.
Here's another question. I have another package that I converted. This one
has 95 email tasks all related to different variables and each to a different
group of recipients if the variable conditions are met. I understand how to
base the email send decision now, but when I converted it I got 95 connection
managers for email. As i clean up the converted package can 1 connection
manager be used for email?

Post by Todd C
Assuming you have totaly redesigned your DTS package as SSIS (and are not
After you add your Send Mail task to the Control Flow and connect to it from
some other task, right-click on the green connecting arrow and select Edit.
In the resulting dialog box you can specify that the down-stream task will
execute based on a Constraint (Success or Failure), an Expression
([User::MyVariable] >= x ) or both.
Note of caution: Some people put Send Mail tasks on their Control Flow and
connect every single other task to it with a Failure constraint. The idea is
that if any taks fails, they want an email to go out. While this *sounds*
like a good idea, it won't work because the Send Mail task would only execute
if EVERY task fails. Better to use the Events tab and create an event handler
for the OnTaskFailed event of the root package.
HTH
--
=====
Todd C

Post by doug
I have an existing DTS package built in SQL 2000 that has a send mail task
dependent on a global variable value. If the value is > x, the email is sent
and this was defined in an ActiveX script in the workflow properties of the
send mail task. I have converted this to a SSIS package in SQL 2005. What
happened to my workflow and how do i set the global variable value to only
send emails when the value reaches this threshold?
Thanks in advance.

[doug]

Thanks for the quick response.

Post by Todd C
Yes, you can consolidate all your Send Mail tasks to use one SMTP Connection
Manager.
You could do it manually via BIDS development environment, in which case you
would need to do it 94 times, and manually delete 94 unused SMTP Connection
Manager.
If you are creative with editing XML via script, you could edit the .dtsx
file outside of SSIS Designer (make a copy first, though!) and loop through
SMTP Connection Managers 2 through 95 and change them to match the first.
HTH
=====
Todd C

Post by doug
Thank you.
Here's another question. I have another package that I converted. This one
has 95 email tasks all related to different variables and each to a different
group of recipients if the variable conditions are met. I understand how to
base the email send decision now, but when I converted it I got 95 connection
managers for email. As i clean up the converted package can 1 connection
manager be used for email?

Post by Todd C
Assuming you have totaly redesigned your DTS package as SSIS (and are not
After you add your Send Mail task to the Control Flow and connect to it from
some other task, right-click on the green connecting arrow and select Edit.
In the resulting dialog box you can specify that the down-stream task will
execute based on a Constraint (Success or Failure), an Expression
([User::MyVariable] >= x ) or both.
Note of caution: Some people put Send Mail tasks on their Control Flow and
connect every single other task to it with a Failure constraint. The idea is
that if any taks fails, they want an email to go out. While this *sounds*
like a good idea, it won't work because the Send Mail task would only execute
if EVERY task fails. Better to use the Events tab and create an event handler
for the OnTaskFailed event of the root package.
HTH
--
=====
Todd C

Post by doug
I have an existing DTS package built in SQL 2000 that has a send mail task
dependent on a global variable value. If the value is > x, the email is sent
and this was defined in an ActiveX script in the workflow properties of the
send mail task. I have converted this to a SSIS package in SQL 2005. What
happened to my workflow and how do i set the global variable value to only
send emails when the value reaches this threshold?
Thanks in advance.