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 Wednesday, February 13, 2013 9:32 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 7:20 AM
Points: 5,180, Visits: 12,033
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.



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

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, October 22, 2014 1:26 AM
Points: 36, Visits: 188

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.
Post #1419624
Posted Wednesday, February 13, 2013 9:51 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 12:58 PM
Points: 7,127, Visits: 12,728
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
Post #1419627
Posted Wednesday, February 13, 2013 10:03 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, October 22, 2014 1:26 AM
Points: 36, Visits: 188
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.
Post #1419636
Posted Wednesday, February 13, 2013 10:17 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 7:20 AM
Points: 5,180, Visits: 12,033
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 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.


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.



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 #1419646
Posted Wednesday, February 13, 2013 10:34 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, October 22, 2014 1:26 AM
Points: 36, Visits: 188

You 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 value
a Null reference exception could result at runtime"

 
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



Post #1419652
Posted Wednesday, February 13, 2013 11:39 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 12:58 PM
Points: 7,127, Visits: 12,728
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 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.

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:

If String.IsNullOrEmpty(Dts.Variables("Attachments").Value.ToString) = False Then
myHtmlMessage.Attachments.Add(New Attachment(Dts.Variables("Attachments").Value.ToString))
End If


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.


__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
Post #1419671
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse