Send Mail on Failure issue
-
Hey guy, new to the forum so a big Hello from me!
I have and issue at the moment that I am struggling to overcome. I Have a DTS set with multiple copy SQL Server Objects steps involved. I also have a step which sends a failure email. So I have it set that if the step fails, the failure message is generated, using the Workflow On Failure option. Is there a way that i can in the email message have the name of the step that is failing?! I'm assuming that this could be a global variable if there is such a way.
Any help would be much appreciated.
Thanks Craig
-
Yes, you can send an email using GlobalVariable.
- Create a GlobalVariable "StepName".
- Set DTSGlobalVariables("StepName").Value="Step1" or something representing the step in the ActiveXScript task.
- Create a Conncetion. This is required for send mail using Execute SQL Task.
- Create an Execute SQL Task with the following script:
DECLARE @MSG varchar(1000)
DECLARE @Subject varchar(1000)
DECLARE @sRecipients varchar(1000)
select @MSG = 'Dear User,' + char(13) + char(13) + 'You have an error in the step [' + ? + ']. Do not respond to this email as it is auto-generated.' + CHAR(13)
select @Subject = 'Package Error Report'
select @sRecipients = 'Your email address'
exec master.dbo.xp_sendmail
@recipients = @sRecipients
,@message = @MSG
,@subject = @Subject
Now you need to set the GlobalVariable to pass into the script.
- Right-click on white space inside your DTS package.
- Select Disconnectd Edit...
- Expand Tasks.
- Select the name of the Execute SQL Task. Do not expand but just click on the name.
- Double-click on the InputGlobalVariableNames under the Property Name in the right pane.
- Edit Property menu comes up.
- Type is string, and the Value is "StepName". Then click OK.
- Close Edit Property menu.
Then connect the ActiveXScript task and the Execute SQL Task using Workfliow [On Failure].
-
For Global Variable: http://msdn2.microsoft.com/en-us/library/aa933470(SQL.80).aspx
For ActiveX Script: http://msdn2.microsoft.com/en-us/library/aa933459(SQL.80).aspx
For Connection: http://msdn2.microsoft.com/en-us/library/aa176223(SQL.80).aspx
For Execute SQL Task: http://msdn2.microsoft.com/en-us/library/aa933511(SQL.80).aspx
For DTS in general: http://msdn2.microsoft.com/en-us/library/aa298646(SQL.80).aspx
-
Terry many thanks as i almost have this working now. I am struggling to get the StepName to appear in the following line:
select @MSG = 'Dear User,' + char(13) + char(13) + 'You have an error in the step [' + ? + ']. Do not respond to this email as it is auto-generated.' + CHAR(13)
Can you help!? I'm presuming I need to replace the '?' with a string!??!
Thanks
Craig
-
No, you do not replace the string but global variable will be replaced automatically. Follow the instruction below but I added this already in the previous mail.
Now you need to set the GlobalVariable to pass into the script.
- Right-click on white space inside your DTS package. - Just right-click anywhere inside the package not on any component.
- Select Disconnectd Edit...
- Expand Tasks.
- Select the name of the Execute SQL Task. Do not expand but just click on the name.
- Double-click on the InputGlobalVariableNames under the Property Name in the right pane.
- Edit Property menu comes up.
- Type is string, and the Value is "StepName". Then click OK. (You need to enter double quote as well.)
- Close Edit Property menu.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply