InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comThu, 23 May 2013 05:09:14 GMT20http://www.sqlservercentral.com/Forums/Topic1418899-364-1.aspx[quote][b]kingdonshel (2/13/2013)[/b][hr]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 [code="plain"] 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()) [/code]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.[/quote]The logic is the problem. You need a mail message in both cases but you do not want to try adding the attachment is your variable is empty. That is what this is saying:[quote]If String.IsNullOrEmpty(Dts.Variables("Attachments").Value.ToString) = False Then myHtmlMessage.Attachments.Add(New Attachment(Dts.Variables("Attachments").Value.ToString))End If[/quote]Are you sure you removed the line that added the attachment and only have it inside the IF block as I show it?We cannot see what you see so we're trying to teach you how to do this on your own. Lookup 'adding a breakpoint in SSIS' and see if you can get into the code to figure what's going on with the variable. No offense but it's clear you're not a .NET developer so you might also look into using the built-in Send Email Task. It supports attachments and you can map your variables to the task's properties.Wed, 13 Feb 2013 11:39:04 GMTopc.threehttp://www.sqlservercentral.com/Forums/Topic1418899-364-1.aspxYou are right I dont know what break ponts are but I have provideda snippet of code below to show how I was trying to approach a solution.Below is another code snippet that I am trying however the error message I get is"Variable 'myHtmlMessage' is used beofre it has been assigned a valuea Null reference exception could result at runtime"[code="vb"] If Not String.IsNullOrEmpty(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()) End If If String.IsNullOrEmpty(Dts.Variables("Attachments").Value.ToString) = False Then myHtmlMessage.Attachments.Add(New Attachment(Dts.Variables("Attachments").Value.ToString)) End If[/code]Wed, 13 Feb 2013 10:34:05 GMTkingdonshelhttp://www.sqlservercentral.com/Forums/Topic1418899-364-1.aspx[quote][b]kingdonshel (2/13/2013)[/b][hr]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 [code="plain"] 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()) [/code]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.[/quote]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.Wed, 13 Feb 2013 10:17:30 GMTPhil Parkinhttp://www.sqlservercentral.com/Forums/Topic1418899-364-1.aspxHi 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 [code="plain"] 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()) [/code]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.Wed, 13 Feb 2013 10:03:52 GMTkingdonshelhttp://www.sqlservercentral.com/Forums/Topic1418899-364-1.aspxTry stepping through the code by setting a breakpoint and running the package, see what the variable contains before evaluating the IF.Wed, 13 Feb 2013 09:51:13 GMTopc.threehttp://www.sqlservercentral.com/Forums/Topic1418899-364-1.aspxI 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.Wed, 13 Feb 2013 09:48:04 GMTkingdonshelhttp://www.sqlservercentral.com/Forums/Topic1418899-364-1.aspxWeird - 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.Wed, 13 Feb 2013 09:32:09 GMTPhil Parkinhttp://www.sqlservercentral.com/Forums/Topic1418899-364-1.aspxHi 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 [code="plain"]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()[/code]Wed, 13 Feb 2013 09:18:38 GMTkingdonshelhttp://www.sqlservercentral.com/Forums/Topic1418899-364-1.aspxTry this instead, changes bolded (edit: and underlined):[font="Courier New"][size="2"][color="black"] [/color][color="blue"]Public Sub [/color][color="black"]Main[/color][color="gray"]()        [/color][color="green"]'MsgBox(Dts.Variables("Username").Value.ToString)'        [/color][color="blue"]Dim [/color][color="black"]myHtmlMessage [/color][color="blue"]As [/color][color="black"]MailMessage        [/color][color="blue"]Dim [/color][color="black"]mySmtpClient [/color][color="blue"]As [/color][color="black"]SmtpClient        [/color][color="green"]'Dim vars As Variables'        [/color][color="blue"]Dim [/color][color="black"]mailAuthentication [/color][color="blue"]As [/color][color="black"]System.Net.NetworkCredential        mailAuthentication [/color][color="blue"]= New [/color][color="black"]System.Net.NetworkCredential[/color][color="gray"]([/color][color="black"]Dts.Variables[/color][color="gray"]([/color][color="darkred"]"Username"[/color][color="gray"])[/color][color="black"].Value.ToString[/color][color="gray"](), [/color][color="black"]Dts.Variables[/color][color="gray"]([/color][color="darkred"]"Password"[/color][color="gray"])[/color][color="black"].Value.ToString[/color][color="gray"]())        [/color][color="black"]myHtmlMessage [/color][color="blue"]= New [/color][color="black"]MailMessage[/color][color="gray"]([/color][color="black"]Dts.Variables[/color][color="gray"]([/color][color="darkred"]"From"[/color][color="gray"])[/color][color="black"].Value.ToString[/color][color="gray"](), [/color][color="black"]Dts.Variables[/color][color="gray"]([/color][color="darkred"]"To"[/color][color="gray"])[/color][color="black"].Value.ToString[/color][color="gray"](), [/color][color="black"]Dts.Variables[/color][color="gray"]([/color][color="darkred"]"Subject"[/color][color="gray"])[/color][color="black"].Value.ToString[/color][color="gray"](), [/color][color="black"]Dts.Variables[/color][color="gray"]([/color][color="darkred"]"Body"[/color][color="gray"])[/color][color="black"].Value.ToString[/color][color="gray"]())[b][u]        [/color][color="blue"]If String[/color][color="black"].IsNullOrEmpty[/color][color="gray"]([/color][color="black"]Dts.Variables[/color][color="gray"]([/color][color="darkred"]"Attachments"[/color][color="gray"])[/color][color="black"].Value.ToString[/color][color="gray"]) [/color][color="blue"]= False Then            [/color][color="black"]myHtmlMessage.Attachments.Add[/color][color="gray"]([/color][color="blue"]New [/color][color="black"]Attachment[/color][color="gray"]([/color][color="black"]Dts.Variables[/color][color="gray"]([/color][color="darkred"]"Attachments"[/color][color="gray"])[/color][color="black"].Value.ToString[/color][color="gray"]))        [/color][color="blue"]End If[/u][/b]        [/color][color="black"]mySmtpClient [/color][color="blue"]= New [/color][color="black"]SmtpClient[/color][color="gray"]([/color][color="black"]Dts.Variables[/color][color="gray"]([/color][color="darkred"]"SMTPServer"[/color][color="gray"])[/color][color="black"].Value.ToString[/color][color="gray"](), [/color][color="black"][b][u]Convert.ToInt32[/color][color="gray"]([/color][color="black"]Dts.Variables[/color][color="gray"]([/color][color="darkred"]"Port"[/color][color="gray"])[/color][color="black"].Value[/color][color="gray"])[/u][/b])        [/color][color="green"]'mySmtpClient.UseDefaultCredentials = False'        [/color][color="black"]mySmtpClient.Credentials [/color][color="blue"]= [/color][color="black"]mailAuthentication        mySmtpClient.EnableSsl [/color][color="blue"]= True        [/color][color="black"]mySmtpClient.Send[/color][color="gray"]([/color][color="black"]myHtmlMessage[/color][color="gray"])        [/color][color="black"]Dts.TaskResult [/color][color="blue"]= [/color][color="black"]ScriptResults.Success    [/color][color="blue"]End Sub[/color][/size][/font]Wed, 13 Feb 2013 08:47:44 GMTopc.threehttp://www.sqlservercentral.com/Forums/Topic1418899-364-1.aspx[quote][b]kingdonshel (2/13/2013)[/b][hr]I tried it but it did not work[/quote]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.Wed, 13 Feb 2013 08:44:53 GMTPhil Parkinhttp://www.sqlservercentral.com/Forums/Topic1418899-364-1.aspxI tried it but it did not workWed, 13 Feb 2013 08:32:49 GMTkingdonshelhttp://www.sqlservercentral.com/Forums/Topic1418899-364-1.aspx:ermm: Did you try the code PMwar posted?Wed, 13 Feb 2013 08:24:06 GMTopc.threehttp://www.sqlservercentral.com/Forums/Topic1418899-364-1.aspxI 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 attachmentsPlease see code below.[code="vb"]Imports SystemImports System.DataImports System.MathImports Microsoft.SqlServer.Dts.RuntimeImports System.Net.MailImports 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.NetworkCredentialmailAuthentication = 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 SubEnd Class[/code]Tue, 12 Feb 2013 08:04:16 GMTkingdonshelhttp://www.sqlservercentral.com/Forums/Topic1418899-364-1.aspx[quote][b]kingdonshel (2/12/2013)[/b][hr]Thanks PMWar I will try this, if you have this solution in C# I wouldn't mind giving that a try as well.[/quote]Whereas I'd like to see the one written in ADA :-DTue, 12 Feb 2013 07:10:38 GMTPhil Parkinhttp://www.sqlservercentral.com/Forums/Topic1418899-364-1.aspxThanks PMWar I will try this, if you have this solution in C# I wouldn't mind giving that a try as well.Tue, 12 Feb 2013 06:14:26 GMTkingdonshelhttp://www.sqlservercentral.com/Forums/Topic1418899-364-1.aspxWrap[code="plain"]myHtmlMessage.Attachments.Add(New Attachment(Dts.Variables("Attachments").Value.ToString))[/code]with an if statement to check if Dts.Variables("Attachments").Value has contents; something along the lines of:[code="plain"]if not isNull(Dts.Variables("Attachments").Value.ToString) myHtmlMessage.Attachments.Add(New Attachment(Dts.Variables("Attachments").Value.ToString))[/code](I can script in other languages, not that fluent with VB; consider this just a starting point)Tue, 12 Feb 2013 06:04:09 GMTPMwarhttp://www.sqlservercentral.com/Forums/Topic1418899-364-1.aspxI 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[code="vb"] Imports SystemImports System.DataImports System.MathImports Microsoft.SqlServer.Dts.RuntimeImports System.Net.MailImports 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.NetworkCredentialmailAuthentication = 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 SubEnd Class [/code]-----------------------------------------------------------------------------------------------Would appreciate any asssitance or known solutions.Tue, 12 Feb 2013 05:39:29 GMTkingdonshel