1
votes

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;
    }
1
Please post the code for the Script Task that's failing.Edmund Schweppe
So I found a work-around but I'm still confused as to what the problem was. If I move the SQL Task that populates the "InternalStr_FieldingDate" variable up the chain of tasks, the package succeeds without braking the script task, but if I have it in its original position (3rd from last -- SQL Task, XML Task, Script Task), it breaks the Script Task. Does anybody know the reason for this? Thank you very much in advance.user2414367

1 Answers

0
votes

If the variable is just to uniquely name the file - then doesn't it seem wasteful to use a SQL task for returning the variable?

A better option would be to calculate the variable from an expression.

Variables have properties just like anything else.

You can set the EvaluateAsExpression property to true and then use any expression you like to figure out the date. As an example:

(DT_WSTR, 4 )YEAR(getdate())
+ RIGHT("0" + (DT_WSTR,2)MONTH(getdate()),2)
+ RIGHT("0" + (DT_WSTR,2)DAY(getdate()),2)

Would return a YYYYMMDD string to use.