Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Help with VB Script for Sending Mail in SSIS Expand / Collapse
Author
Message
Posted Tuesday, February 12, 2013 5:39 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, November 5, 2014 10:30 AM
Points: 37, Visits: 197
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.
Post #1418899
Posted Tuesday, February 12, 2013 6:04 AM


SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Wednesday, April 17, 2013 2:26 AM
Points: 87, 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
Post #1418911
Posted Tuesday, February 12, 2013 6:14 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, November 5, 2014 10:30 AM
Points: 37, Visits: 197

Thanks PMWar I will try this, if you have this solution in C# I wouldn't mind giving that a try as well.
Post #1418920
Posted Tuesday, February 12, 2013 7:10 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 11:02 PM
Points: 5,259, Visits: 12,193
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



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

When you ask a question (and please do ask a question: "My T-SQL does not work" just doesn't cut it), please provide enough information for us to understand its context.
Post #1418948
Posted Tuesday, February 12, 2013 8:04 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, November 5, 2014 10:30 AM
Points: 37, Visits: 197
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

Post #1418988
Posted Wednesday, February 13, 2013 8:24 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 1:58 PM
Points: 7,135, Visits: 12,748
Did you try the code PMwar posted?

__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
Post #1419559
Posted Wednesday, February 13, 2013 8:32 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, November 5, 2014 10:30 AM
Points: 37, Visits: 197
I tried it but it did not work
Post #1419569
Posted Wednesday, February 13, 2013 8:44 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 11:02 PM
Points: 5,259, Visits: 12,193
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.

When you ask a question (and please do ask a question: "My T-SQL does not work" just doesn't cut it), please provide enough information for us to understand its context.
Post #1419582
Posted Wednesday, February 13, 2013 8:47 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 1:58 PM
Points: 7,135, Visits: 12,748
Try this instead, changes bolded (edit: and underlined):


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


        
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
Post #1419584
Posted Wednesday, February 13, 2013 9:18 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, November 5, 2014 10:30 AM
Points: 37, Visits: 197
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()

Post #1419603
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse