I have an SSIS package that runs several SQL stored procedures, file tasks, and an xml and a script task. The final script task sends an email with an html table, Excel file attachment, and an email body. I have a variable that is used to uniquely label the Excel file, on a daily basis, as well as to uniquely label the email's subject line. This variable is simply the current date. However, the date was originally "hard-coded" in the variable's value. However, I decided instead to add an Execute SQL Task, which returns and formats the current date. Then populates that daily variable.
The problem I am encountering is that when the variable's value field is empty, the script task (which sends out the email) fails. Even when it's not empty, the script task fails. It only succeeds when the current date's value is entered. So it only works when the value is equal to the return value of the SQL Task that populates the variable.
However, the Script task grabs the SQL Task's return value, rather than the variable's "hard-coded" value. I know this because I can write 23 august, 2013
(no caps), in the variable's value section, and the value that is used in the email and file extension is still 23 August, 2013
(capitalized) -- the exact return value of the SQL Task.
My question is, which I have not found much helpful information on in my research, is, "Why is this happening?" Having to write in the value on a daily basis defeats the purpose of having the SQL Task [which populates the variable] in there to begin with. Furthermore, please note that when I added the SQL task in originally, the package succeeded even with an empty variable value. It wasn't until after I had made several additions to the package that I ran into this dilemma. Any comments, suggestions, and advice will be highly appreciated. Thank you very much.
The code for the failing script task is listed below. The daily variable I spoke of is named InternalStr_FieldingDate
public void Main()
{
String EmailMsgBody = String.Format("<HTML><BODY><P>{0}</P><P>{1}</P></BODY></HTML>"
, Dts.Variables["Config_SMTP_MessageSourceText"].Value.ToString()
, Dts.Variables["InternalStr_CountResultAfterXSLT"].Value.ToString());
MailMessage EmailCountMsg = new MailMessage(Dts.Variables["Config_SMTP_From"].Value.ToString().Replace(";", ",")
, Dts.Variables["Config_SMTP_Success_To"].Value.ToString().Replace(";", ",")
, Dts.Variables["Config_SMTP_SubjectLinePrefix"].Value.ToString() + " " + Dts.Variables["InternalStr_FieldingDate"].Value.ToString()
, EmailMsgBody);
//Add Attachment
String filename = Dts.Variables["fileDailyReport"].Value.ToString();
System.Net.Mail.Attachment attachment;
attachment = new System.Net.Mail.Attachment(filename);
EmailCountMsg.Attachments.Add(attachment);
//EmailCountMsg.From.
EmailCountMsg.CC.Add(Dts.Variables["Config_SMTP_Success_CC"].Value.ToString().Replace(";", ","));
EmailCountMsg.IsBodyHtml = true;
SmtpClient SMTPForCount = new SmtpClient(Dts.Variables["Config_SMTP_ServerAddress"].Value.ToString());
SMTPForCount.Credentials = CredentialCache.DefaultNetworkCredentials;
SMTPForCount.Send(EmailCountMsg);
Dts.TaskResult = (int)ScriptResults.Success;
}