CODEWITHSUNDEEP
c#sqlvariablesssis
user2414367
user2414367

Reputation: 11

SSIS string variable fails script Task

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;
    }

Upvotes: 1

Views: 1833

Answers (1)

Stephen
Stephen

Reputation: 103

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.

Upvotes: 0

Related Questions