CODEWITHSUNDEEP
ssis
Saurabh Kulkarni
Saurabh Kulkarni

Reputation: 69

How to add condition in SSIS package to send mails only if attachments are available

I have created an SSIS package that includes one script task to add new attachment name and path in a variable after "|" and running it under foreach loop to include all attachment names and path in the variable value. then I am passing that variable as an attachment to send mail task. This package is running fine via batch file execution and sending one email containing multiple files as well.

Now, I want to schedule that batch file to run on every hour, and to accomplish that, I need to add logic in the package to send mail only if 2 attachments are available. It should not send any email if there are no or single attachment present. that way I want to remove manual job execution. Can you please help? I am new to SSIS development. Script task code as below:

if (Dts.Variables["User::FileNamesList"].Value.ToString() == "")
            {
                Dts.Variables["User::FileNamesList"].Value = Dts.Variables["User::InputPath"].Value.ToString() + Dts.Variables["User::Year"].Value.ToString() +"\\" + Dts.Variables["User::FileName"].Value.ToString();
            }
            else
            { 
                Dts.Variables["User::FileNamesList"].Value = Dts.Variables["User::FileNamesList"].Value.ToString() + "|" + Dts.Variables["User::InputPath"].Value.ToString() + Dts.Variables["User::Year"].Value.ToString() + "\\" + Dts.Variables["User::FileName"].Value.ToString();
            }

Image of the package

Upvotes: 0

Views: 1430

Answers (2)

Saurabh Kulkarni
Saurabh Kulkarni

Reputation: 69

This was resolved on same day. I was able add variable in my script task to count number of files (basically it will increase by 1 on each loop execution), then I added a simple condition into arrow connecting to mail send task to first check if the variable value is matching with expected values (created a variable in SSIS and assigned max expected file count), so it now it will send mail only if 2 files are available in folder. I have added a block only just to reset the variables and that also has second arrow attached from foreach loop , to run if the condition is not matching. The tool is now sending mail only for year which will have both files present or skip for that loop. I was able to schedule that as well.

Arrow condition true: before send mail task: enter image description here

condition in arrow for script task to resent variable 2: enter image description here

Counter: enter image description here

enter image description here

Upvotes: 0

KeithL
KeithL

Reputation: 5594

Anything involving mail is much better handled in script task.

Let me explain "better". You have a lot more control on To, Cc, Bcc, ReplyTo, etc. As well as being able to send a html formatted body.

This should run your entire application:

    {
        var fis = Directory.GetFiles(@"C:\folder"); //a 2nd param can be added for search pattern (ex. "*.xls")
        //Check for number of files
        if(fis.Length>1)
        {
            SendEmail("Where's it going", "Here are some files", "What you want in the body", fis);
            //Archive
            foreach (var f in fis)
                File.Move(f, @"archive folder path" + new FileInfo(f).Name);
        }

    }

    public static void SendEmail(string to, string subject, string body, string[] filePaths)
    {
        string from = "[email protected]";
        
        using (MailMessage mail = new MailMessage())
        {
            SmtpClient SmtpServer = new SmtpClient("YourSMTPClient");

            mail.From = new MailAddress(from);

            mail.To.Add(new MailAddress(to));

            mail.Subject = subject;

            mail.IsBodyHtml = true;
            mail.Body = body;

            foreach (var f in filePaths)
            {
                Attachment file;
                file = new Attachment(f);
                mail.Attachments.Add(file);
            }

            SmtpServer.Port = 9999; //Your SMTP port
            SmtpServer.Credentials = new NetworkCredential(from, pword);

            SmtpServer.EnableSsl = true;

            SmtpServer.Send(mail);
        }
    }

Upvotes: 1

Related Questions