Help with VB Script for Sending Mail in SSIS

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Data Warehousing » Integration Services » Help with VB Script for Sending Mail in SSIS

17 posts, Page 1 of 2

1 2 »»

Help with VB Script for Sending Mail in SSIS

Rate Topic

Display Mode

Topic Options

Author

Message

kingdonshel

Posted Tuesday, February 12, 2013 5:39 AM

SSC Rookie

Group: General Forum Members
Last Login: Wednesday, February 20, 2013 1:05 AM
Points: 30, Visits: 134

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

PMwar

Posted Tuesday, February 12, 2013 6:04 AM

SSC 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

kingdonshel

Posted Tuesday, February 12, 2013 6:14 AM

SSC Rookie

Group: General Forum Members
Last Login: Wednesday, February 20, 2013 1:05 AM
Points: 30, Visits: 134

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

Phil Parkin

Posted Tuesday, February 12, 2013 7:10 AM

SSCarpal Tunnel

Group: General Forum Members
Last Login: Yesterday @ 5:12 AM
Points: 4,226, Visits: 9,458

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 BigGrin

____________________________________________________________________________________________

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

http://www.sqlservercentral.com/articles/Best+Practices/61537/

If you are asking for help and your post does not contain a question, you should expect responses which do not contain any answers. Put a question mark in there somewhere - it's not rocket science.

Post #1418948

kingdonshel

Posted Tuesday, February 12, 2013 8:04 AM

SSC Rookie

Group: General Forum Members
Last Login: Wednesday, February 20, 2013 1:05 AM
Points: 30, Visits: 134

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

opc.three

Posted Wednesday, February 13, 2013 8:24 AM

SSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 6:47 PM
Points: 6,693, Visits: 11,704

Did you try the code PMwar posted?

__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato

Believe you can and you're halfway there. --Theodore Roosevelt

Everything Should Be Made as Simple as Possible, But Not Simpler --Albert Einstein

The significant problems we face cannot be solved at the same level of thinking we were at when we created them. --Albert Einstein

1 apple is not exactly 1/8 of 8 apples. Because there are no absolutely identical apples. --Giordy

Post #1419559

kingdonshel

Posted Wednesday, February 13, 2013 8:32 AM

SSC Rookie

Group: General Forum Members
Last Login: Wednesday, February 20, 2013 1:05 AM
Points: 30, Visits: 134

I tried it but it did not work

Post #1419569

Phil Parkin

Posted Wednesday, February 13, 2013 8:44 AM

SSCarpal Tunnel

Group: General Forum Members
Last Login: Yesterday @ 5:12 AM
Points: 4,226, Visits: 9,458

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:

http://www.sqlservercentral.com/articles/Best+Practices/61537/

If you are asking for help and your post does not contain a question, you should expect responses which do not contain any answers. Put a question mark in there somewhere - it's not rocket science.

Post #1419582

opc.three

Posted Wednesday, February 13, 2013 8:47 AM

SSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 6:47 PM
Points: 6,693, Visits: 11,704

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

Believe you can and you're halfway there. --Theodore Roosevelt

Everything Should Be Made as Simple as Possible, But Not Simpler --Albert Einstein

The significant problems we face cannot be solved at the same level of thinking we were at when we created them. --Albert Einstein

1 apple is not exactly 1/8 of 8 apples. Because there are no absolutely identical apples. --Giordy

Post #1419584

kingdonshel

Posted Wednesday, February 13, 2013 9:18 AM

SSC Rookie

Group: General Forum Members
Last Login: Wednesday, February 20, 2013 1:05 AM
Points: 30, Visits: 134

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 »

17 posts, Page 1 of 2

1 2 »»

Permissions