February 12, 2013 at 5:39 am
I have managed to send email from my SSIS package using the script task with VB code.
I am not good at scripting, I basically looked at various scripts online to get me to where I am.
The problem I have is that I have set up all my variables in my package and I have set them to ReadOnly in my package.
Everything works fine if I specify a value for my attachment parameter but if I dont my my script fail. I donrt always want to send an attached file.
I would like the code to be modified so that if there is no value specified for my Attachement variable them my script task will only send the mesage in the body and not fail
Also if possible when, attachments are sent would like to be able to send multible attachments.
see the code for my scrpt below
Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Runtime
Imports System.Net.Mail
Imports System.Net
<System.AddIn.AddIn("ScriptMain", Version:="1.0", Publisher:="", Description:="")> _
<System.CLSCompliantAttribute(False)> _
Partial Public Class ScriptMain
Inherits Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
Enum ScriptResults
Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success
Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
End Enum
Public Sub Main()
'MsgBox(Dts.Variables("Username").Value.ToString)'
Dim myHtmlMessage As MailMessage
Dim mySmtpClient As SmtpClient
'Dim vars As Variables'
Dim mailAuthentication As System.Net.NetworkCredential
mailAuthentication = New System.Net.NetworkCredential(Dts.Variables("Username").Value.ToString(), Dts.Variables("Password").Value.ToString())
myHtmlMessage = New MailMessage(Dts.Variables("From").Value.ToString(), Dts.Variables("To").Value.ToString(), Dts.Variables("Subject").Value.ToString(), Dts.Variables("Body").Value.ToString())
myHtmlMessage.Attachments.Add(New Attachment(Dts.Variables("Attachments").Value.ToString))
mySmtpClient = New SmtpClient(Dts.Variables("SMTPServer").Value.ToString(), Dts.Variables("Port").Value.ToString())
'mySmtpClient.UseDefaultCredentials = False'
mySmtpClient.Credentials = mailAuthentication
mySmtpClient.EnableSsl = True
mySmtpClient.Send(myHtmlMessage)
Dts.TaskResult = ScriptResults.Success
End Sub
End Class
-----------------------------------------------------------------------------------------------
Would appreciate any asssitance or known solutions.
February 12, 2013 at 6:04 am
Wrap
myHtmlMessage.Attachments.Add(New Attachment(Dts.Variables("Attachments").Value.ToString))
with an if statement to check if Dts.Variables("Attachments").Value has contents; something along the lines of:
if not isNull(Dts.Variables("Attachments").Value.ToString)
myHtmlMessage.Attachments.Add(New Attachment(Dts.Variables("Attachments").Value.ToString))
(I can script in other languages, not that fluent with VB; consider this just a starting point)
PMWar
February 12, 2013 at 6:14 am
Thanks PMWar I will try this, if you have this solution in C# I wouldn't mind giving that a try as well.
February 12, 2013 at 7:10 am
kingdonshel (2/12/2013)
Thanks PMWar I will try this, if you have this solution in C# I wouldn't mind giving that a try as well.
Whereas I'd like to see the one written in ADA 😀
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
February 12, 2013 at 8:04 am
I have formated and reposted my code to make it easier to read, as stated in my post before, my code below works only if I have a value for my @Attachment variable in VS SSIS.
I would like my script to work even if I have no attachment , I dont always want to have an attachment when sending an email, is there a way to recode my script to allow this to be possible.
Also if possible when, attachments are sent would like to be able to send multible attachments
Please see code below.
Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Runtime
Imports System.Net.Mail
Imports System.Net
<System.AddIn.AddIn("ScriptMain", Version:="1.0", Publisher:="", Description:="")> _
<System.CLSCompliantAttribute(False)> _
Partial Public Class ScriptMain
Inherits Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
Enum ScriptResults
Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success
Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
End Enum
Public Sub Main()
'MsgBox(Dts.Variables("Username").Value.ToString)'
Dim myHtmlMessage As MailMessage
Dim mySmtpClient As SmtpClient
'Dim vars As Variables'
Dim mailAuthentication As System.Net.NetworkCredential
mailAuthentication = New System.Net.NetworkCredential(Dts.Variables("Username").Value.ToString(), Dts.Variables("Password").Value.ToString())
myHtmlMessage = New MailMessage(Dts.Variables("From").Value.ToString(), Dts.Variables("To").Value.ToString(), Dts.Variables("Subject").Value.ToString(), Dts.Variables("Body").Value.ToString())
myHtmlMessage.Attachments.Add(New Attachment(Dts.Variables("Attachments").Value.ToString))
mySmtpClient = New SmtpClient(Dts.Variables("SMTPServer").Value.ToString(), Dts.Variables("Port").Value.ToString())
'mySmtpClient.UseDefaultCredentials = False'
mySmtpClient.Credentials = mailAuthentication
mySmtpClient.EnableSsl = True
mySmtpClient.Send(myHtmlMessage)
Dts.TaskResult = ScriptResults.Success
End Sub
End Class
February 13, 2013 at 8:24 am
:ermm: Did you try the code PMwar posted?
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
February 13, 2013 at 8:32 am
I tried it but it did not work
February 13, 2013 at 8:44 am
kingdonshel (2/13/2013)
I tried it but it did not work
You will find that people will respond better if you post error messages and what you have tried to resolve them.
Very few SSIS developers have the supernatural powers necessary to be able to solve 'it did not work' problems without additional information.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
February 13, 2013 at 8:47 am
Try this instead, changes bolded (edit: and underlined):
[font="Courier New"]
Public Sub Main()
'MsgBox(Dts.Variables("Username").Value.ToString)'
Dim myHtmlMessage As MailMessage
Dim mySmtpClient As SmtpClient
'Dim vars As Variables'
Dim mailAuthentication As System.Net.NetworkCredential
mailAuthentication = New System.Net.NetworkCredential(Dts.Variables("Username").Value.ToString(), Dts.Variables("Password").Value.ToString())
myHtmlMessage = New MailMessage(Dts.Variables("From").Value.ToString(), Dts.Variables("To").Value.ToString(), Dts.Variables("Subject").Value.ToString(), Dts.Variables("Body").Value.ToString())
If String.IsNullOrEmpty(Dts.Variables("Attachments").Value.ToString) = False Then
myHtmlMessage.Attachments.Add(New Attachment(Dts.Variables("Attachments").Value.ToString))
End If[/u][/b]
mySmtpClient = New SmtpClient(Dts.Variables("SMTPServer").Value.ToString(), Convert.ToInt32(Dts.Variables("Port").Value)[/u][/b])
'mySmtpClient.UseDefaultCredentials = False'
mySmtpClient.Credentials = mailAuthentication
mySmtpClient.EnableSsl = True
mySmtpClient.Send(myHtmlMessage)
Dts.TaskResult = ScriptResults.Success
End Sub[/font]
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
February 13, 2013 at 9:18 am
Hi opcthree, and to others who are willingly trying to assists. I have posted the error messg below based on comments from Phil.
With your code I get the same error message if I dont put a value in SSIS for the Attachment @parameter, even with your new code if I dont put an attachemnt value, the package fails with the below error messg.
With your code if I put a value for eg E:\Files\Skills\SSIS_training\t.txt the the email is sent to my mail box with the attached file.
I am trying to find a way that I can still get an email even when the attachment parameter is empty since I want to use this script repeatidly but not always wanting to send an attachemnt with the email all the time.
See error messg from package excecution
Error: System.Reflection.TargetInvocationException: Exception has been thrown by the target of an invocation. ---> System.ArgumentException: The parameter 'fileName' cannot be an empty string.
Parameter name: fileName
at System.Net.Mail.AttachmentBase.SetContentFromFile(String fileName, String mediaType)
at System.Net.Mail.AttachmentBase..ctor(String fileName)
at System.Net.Mail.Attachment..ctor(String fileName)
at ST_773c6c495bb046feb375a17c3676351c.vbproj.ScriptMain.Main()
--- End of inner exception stack trace ---
at System.RuntimeMethodHandle._InvokeMethodFast(Object target, Object[] arguments, SignatureStruct& sig, MethodAttributes methodAttributes, RuntimeTypeHandle typeOwner)
at System.RuntimeMethodHandle.InvokeMethodFast(Object target, Object[] arguments, Signature sig, MethodAttributes methodAttributes, RuntimeTypeHandle typeOwner)
at System.Reflection.RuntimeMethodInfo.Invoke(Object obj, BindingFlags invokeAttr, Binder binder, Object[] parameters, CultureInfo culture, Boolean skipVisibilityChecks)
at System.Reflection.RuntimeMethodInfo.Invoke(Object obj, BindingFlags invokeAttr, Binder binder, Object[] parameters, CultureInfo culture)
at System.RuntimeType.InvokeMember(String name, BindingFlags bindingFlags, Binder binder, Object target, Object[] providedArgs, ParameterModifier[] modifiers, CultureInfo culture, String[] namedParams)
at System.Type.InvokeMember(String name, BindingFlags invokeAttr, Binder binder, Object target, Object[] args, CultureInfo culture)
at Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTATaskScriptingEngine.ExecuteScript()
February 13, 2013 at 9:32 am
Weird - have you tried stepping through your code in debug mode to verify exactly what is in your attachment variable?
The code provided looks like it should work.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
February 13, 2013 at 9:48 am
I have provided an eg of my the value for my Attachment variable in my previous post, all it is, is a filepath to the file I wish to attach.
Like I said the code works but only if you provided a value in the @Attachment variable in SSIS, however I dont always want to or need to have an attachment. I did not always want to remove the attachment parameter from my package when I am not sending an attachment in the email.
I was planning to use this script task as a way to send all types of email or even more than one attachment if necessary.
I thought this would have been something that many people had tried before but my vb skills are not good so I tried getting help.
February 13, 2013 at 9:51 am
Try stepping through the code by setting a breakpoint and running the package, see what the variable contains before evaluating the IF.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
February 13, 2013 at 10:03 am
Hi opc.three, the truth is your code makes no difference the code works in the same way with or without your code as long as the file path is not specified for the Attacjhment variable it does not work
Whai I was lookinf for was something like
IF myHtmlMessage.Attachments.Add(New Attachment(Dts.Variables("Attachments").Value.ToString)) = ""
Then
myHtmlMessage = New MailMessage(Dts.Variables("From").Value.ToString(), Dts.Variables("To").Value.ToString(), Dts.Variables("Subject").Value.ToString(), Dts.Variables("Body").Value.ToString())
I know the syntax might be wrong but basically if there is no attachment then just send message with the other variables.
I just cant code as good as I would like to so I cant seem to get this working.
February 13, 2013 at 10:17 am
kingdonshel (2/13/2013)
Hi opc.three, the truth is your code makes no difference the code works in the same way with or without your code as long as the file path is not specified for the Attacjhment variable it does not workWhai I was lookinf for was something like
IF myHtmlMessage.Attachments.Add(New Attachment(Dts.Variables("Attachments").Value.ToString)) = ""Then
myHtmlMessage = New MailMessage(Dts.Variables("From").Value.ToString(), Dts.Variables("To").Value.ToString(), Dts.Variables("Subject").Value.ToString(), Dts.Variables("Body").Value.ToString())
I know the syntax might be wrong but basically if there is no attachment then just send message with the other variables.
I just cant code as good as I would like to so I cant seem to get this working.
I see that you are back to the 'it doesn't work mentality'.
If you don't understand debugging and breakpoints, that's OK - just say so and we can try to help.
But it seems that you're just ignoring our suggestions.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply