SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Help with VB Script for Sending Mail in SSIS


Help with VB Script for Sending Mail in SSIS

Author
Message
kingdonshel
kingdonshel
SSC-Enthusiastic
SSC-Enthusiastic (142 reputation)SSC-Enthusiastic (142 reputation)SSC-Enthusiastic (142 reputation)SSC-Enthusiastic (142 reputation)SSC-Enthusiastic (142 reputation)SSC-Enthusiastic (142 reputation)SSC-Enthusiastic (142 reputation)SSC-Enthusiastic (142 reputation)

Group: General Forum Members
Points: 142 Visits: 216
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.
PMwar
PMwar
SSC Journeyman
SSC Journeyman (97 reputation)SSC Journeyman (97 reputation)SSC Journeyman (97 reputation)SSC Journeyman (97 reputation)SSC Journeyman (97 reputation)SSC Journeyman (97 reputation)SSC Journeyman (97 reputation)SSC Journeyman (97 reputation)

Group: General Forum Members
Points: 97 Visits: 228
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
kingdonshel
kingdonshel
SSC-Enthusiastic
SSC-Enthusiastic (142 reputation)SSC-Enthusiastic (142 reputation)SSC-Enthusiastic (142 reputation)SSC-Enthusiastic (142 reputation)SSC-Enthusiastic (142 reputation)SSC-Enthusiastic (142 reputation)SSC-Enthusiastic (142 reputation)SSC-Enthusiastic (142 reputation)

Group: General Forum Members
Points: 142 Visits: 216
Thanks PMWar I will try this, if you have this solution in C# I wouldn't mind giving that a try as well.
Phil Parkin
Phil Parkin
SSCoach
SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)

Group: General Forum Members
Points: 18831 Visits: 20460
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 :-D


Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

Please surround any code or links you post with the appropriate IFCode formatting tags. It helps readability a lot.
kingdonshel
kingdonshel
SSC-Enthusiastic
SSC-Enthusiastic (142 reputation)SSC-Enthusiastic (142 reputation)SSC-Enthusiastic (142 reputation)SSC-Enthusiastic (142 reputation)SSC-Enthusiastic (142 reputation)SSC-Enthusiastic (142 reputation)SSC-Enthusiastic (142 reputation)SSC-Enthusiastic (142 reputation)

Group: General Forum Members
Points: 142 Visits: 216
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


Orlando Colamatteo
Orlando Colamatteo
SSCoach
SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)

Group: General Forum Members
Points: 15065 Visits: 14396
Ermm Did you try the code PMwar posted?

__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
kingdonshel
kingdonshel
SSC-Enthusiastic
SSC-Enthusiastic (142 reputation)SSC-Enthusiastic (142 reputation)SSC-Enthusiastic (142 reputation)SSC-Enthusiastic (142 reputation)SSC-Enthusiastic (142 reputation)SSC-Enthusiastic (142 reputation)SSC-Enthusiastic (142 reputation)SSC-Enthusiastic (142 reputation)

Group: General Forum Members
Points: 142 Visits: 216
I tried it but it did not work
Phil Parkin
Phil Parkin
SSCoach
SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)

Group: General Forum Members
Points: 18831 Visits: 20460
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.


Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

Please surround any code or links you post with the appropriate IFCode formatting tags. It helps readability a lot.
Orlando Colamatteo
Orlando Colamatteo
SSCoach
SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)

Group: General Forum Members
Points: 15065 Visits: 14396
Try this instead, changes bolded (edit: and underlined):


Public Sub Main()
        
'MsgBox(Dts.Variables("Username"Wink.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


        
mySmtpClient = New SmtpClient(Dts.Variables("SMTPServer").Value.ToString(), Convert.ToInt32(Dts.Variables("Port").Value))

        
'mySmtpClient.UseDefaultCredentials = False'


        
mySmtpClient.Credentials = mailAuthentication

        mySmtpClient.EnableSsl
= True

        
mySmtpClient.Send(myHtmlMessage)

        
Dts.TaskResult = ScriptResults.Success

    
End Sub


__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
kingdonshel
kingdonshel
SSC-Enthusiastic
SSC-Enthusiastic (142 reputation)SSC-Enthusiastic (142 reputation)SSC-Enthusiastic (142 reputation)SSC-Enthusiastic (142 reputation)SSC-Enthusiastic (142 reputation)SSC-Enthusiastic (142 reputation)SSC-Enthusiastic (142 reputation)SSC-Enthusiastic (142 reputation)

Group: General Forum Members
Points: 142 Visits: 216
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()


Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search