Redirect error rows to email

Christopher Jack 1,611 Reputation points
2020-12-11T09:59:59.493+00:00

Hi,

In SSIS I am routing error rows to a text file to highlight incorrect rows.

Is there a way to email the rows on error?

Thanks

Chris

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,612 questions
SQL Server Integration Services
SQL Server Integration Services
A Microsoft platform for building enterprise-level data integration and data transformations solutions.
2,449 questions
0 comments No comments
{count} votes

2 answers

Sort by: Most helpful
  1. Monalv-MSFT 5,891 Reputation points
    2020-12-14T03:58:08.533+00:00

    Hi @Christopher Jack ,

    1.Please add error flow redirection in Data Flow Task.
    47813-configureerroroutput.png
    47851-errorredirect.png

    2.Please use Script Task to send the error text file to email. We can use the following script in Script Task:

    public void Main()  
            {  
    
                string subject = "Package Information";  
                string body = " ";  
                string MailFrom = " @outlook.com";  
                string MailTo = " @outlook.com";  
                string file = @"C:\Test\ErrorRows.txt"; //Add attach File  
                string password = " "; //MailFrom password  
                int port = 587;  
                string server = "smtp.live.com";  
                string userName = " @outlook.com"; //MailFrom  
                Boolean IsBodyHtml = true;  
    
                SendMailMessage(MailFrom, MailTo, subject, body, IsBodyHtml, server, port, userName, password, file); //Add file  
    
                Dts.TaskResult = (int)ScriptResults.Success;  
            }  
    
            public void SendMailMessage(string From, string SendTo, string Subject, string Body, Boolean IsBodyHtml, string Server, int Port, string UserName, string Password, string file) //Add file  
            {  
                MailMessage htmlMessage;  
                SmtpClient mySmtpClient;  
                try  
                {  
                    htmlMessage = new MailMessage(From, SendTo, Subject, Body);  
                    htmlMessage.IsBodyHtml = IsBodyHtml;  
                    htmlMessage.Attachments.Add(new Attachment(file)); //Add file  
                    mySmtpClient = new SmtpClient(Server, Port);  
                    mySmtpClient.DeliveryMethod = SmtpDeliveryMethod.Network;  
                    mySmtpClient.UseDefaultCredentials = false;  
                    mySmtpClient.Credentials = new System.Net.NetworkCredential(UserName, Password);  
    
                    mySmtpClient.EnableSsl = true;  
                    mySmtpClient.Send(htmlMessage);  
                }  
                catch (Exception ex)  
                {  
                    MessageBox.Show(ex.ToString());  
                }  
    
            }  
    

    Best Regards,
    Mona


    If the answer is helpful, please click "Accept Answer" and upvote it.
    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.
    Hot issues in November--What can I do if my transaction log is full?
    Hot issues in November--How to convert Profiler trace into a SQL Server table?

    1 person found this answer helpful.

  2. Olaf Helper 40,656 Reputation points
    2020-12-11T13:11:37.443+00:00

    In SSIS I am routing error rows to a text file to highlight incorrect rows.

    You can use a Send Mail Task to send the create text file with the error to your email address.

    0 comments No comments