Help with VB Script for Sending Mail in SSIS

• kingdonshel

  SSCommitted

  Points: 1704

  More actions

  February 13, 2013 at 10:34 am

  #1587580

  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

• Orlando Colamatteo

  SSC Guru

  Points: 182279

  More actions

  February 13, 2013 at 11:39 am

  #1587595

  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