Dom Vito
Dom Vito

Reputation: 567

How to send an email in SSIS to recipient based on lookup table?

I am wanting to create a Send Mail Task in SSIS inside of an event handler, for when a file in a for each loop does not process as intended. The file is in .xlsx format. In the file name (or in the rows) will be an Account ID that I want to use to reference a table. That table has two columns, AccountID and email address. The email address is where I want to send the email when that file fails to be processed.

I am relatively new to creating these kind of tasks in SSIS so I will need some help. I am mostly worried about how to reference the table in the task... thinking I may need to have multiple steps? Let me know if you need more context or information, and thanks in advance for your help!

Upvotes: 0

Views: 591

Answers (2)

KeithL
KeithL

Reputation: 5594

Here's an answer to this issue:

I am mostly worried about how to reference the table in the task

  1. Get account number into a variable some how. If it is in filename (this is best target for string manipulation.

  2. Add a execute SQL, and set the SQL to "select email from [your table] where account = ?"

  3. Set result set to single row

  4. Parameter Map you variable from Step 1

  5. Map your result to a "email varaible"

  6. Use that email variable to send mail somehow (either send Mail task or script task or SQL task through DB Mail). My preference is c# script task as you have much more control.

Upvotes: 0

paone
paone

Reputation: 937

From information provided you have to use ssis script task or send email task with variables getting the emailAddress from the reference table.

//SSIS script task
        public void Main()
        {
            SmtpClient varSMTPClient;

            string varEmailFrom = Dts.Variables["$Project::projectParameterEmailFrom"].Value.ToString();// readonly variable
            string varEmailTo = Dts.Variables["$Project::projectParameterEmailTo"].Value.ToString();// readonly variable

            MailMessage varHTMLMAIL = new MailMessage(varEmailFrom, varEmailTo);
            varHTMLMAIL.IsBodyHtml = true;
            varHTMLMAIL.Subject = "SSIS send e-mail";

            varHTMLMAIL.Body = "e-mail body text";
            varSMTPClient = new SmtpClient("SMTP Servername");
            varSMTPClient.UseDefaultCredentials = true;
            varSMTPClient.Send(varHTMLMAIL);

            Dts.TaskResult = (int)ScriptResults.Success;
        }

Upvotes: 2

Related Questions