ssis error handling

Posted on 2012-08-16
Last Modified: 2012-08-30
I have a data flow task with multiple components.. I want to send an email to myself if one of the components fail but the "send mail task" is not available when you go to dataflow.. How do I do this? I can never find the topic SSIS_SSAS here..
Question by:cheryl9063
    LVL 11

    Expert Comment

    hI ,

     you can do only the below steps in component level in Data flow

    Fail Component: The Data Flow task fails when an error or a truncation occurs. Failure is the default option for an error and a truncation.

    Ignore Failure: The error or the truncation is ignored and the data row is directed to the output of the transformation or source.

    Redirect Row: The error or the truncation data row is directed to the error output of the source, transformation, or destination.
    LVL 21

    Accepted Solution


    The "data flow task" itself is a task at the control flow, and you should use the "send mail task" at the the control flow flow to alert you if the data flow at all has failed, by connecting the red constraint out of the "data flow task" to a "send mail task"

    If you really want to target a specific component in the "data flow task",   you can use the "OLE DB Command" to target a parametrized stored procedure, that uses the "sp_send_dbmail", you'll need to configure DB mail and create a profile.

    DFT E-mail
    Let me know if you need any help with any of the steps.
    LVL 1

    Author Comment

    "you can use the "OLE DB Command" to target a parametrized stored procedure, that uses the "sp_send_dbmail", you'll need to configure DB mail and create a profile." This sounds like a good idea.. Ole DB destination? Tell me more about this please?
    LVL 21

    Expert Comment


    You can search for "SSIS OLE DB Command" and you'll find many articles talking about it, basically it's similar to the "execute sql task" but in the "data flow task", can accept variables too as input parameters to your stored procedure.  check that post LINK

    so in my screen shot above, I'm doing a lookup against a table and when no match found, I send myself an alert e-mail, so my SP accepts a subject and body parameters and I pass these variables from SSIS.

    and my stored procedure would look like that...

    USE [CLGWarehouseProd]
    /****** Object:  StoredProcedure [etl].[sendEmailAlert]    Script Date: 08/21/2012 08:43:21 ******/
    ALTER PROCEDURE [etl].[sendEmailAlert]
    @EmailSubject		VARCHAR (100),
    @EmailMessage       VARCHAR (500)
    @EmailRecipients	VARCHAR (300),
    @CopyRecipients		VARCHAR (100),
    @EmailProfile		VARCHAR (100)
    SET @EmailRecipients = ''
    SET @CopyRecipients = ''
    SET @EmailProfile = 'OurDatabaseMailProfile'
    -- ##SUMMARY This procedure does the following: 
    -- ##REMARKS Parameters: 
    -- ##REMARKS Execution Syntax: EXEC 
    -- ##REMARKS 
    -- Created: 5/19/07
    -- Created by: Jason yousef
    -- Project: FTP Download Utility
    		DECLARE @FullSubject nvarchar(241);
    		SELECT @FullSubject = @EmailSubject + ' (' + CAST(@@SERVERNAME AS nvarchar(241)) + ')';
    		EXEC msdb..sp_send_dbmail @profile_name = @EmailProfile,
    			@recipients = @EmailRecipients,
    			@copy_recipients = @CopyRecipients,
    			@subject = @FullSubject,
    			@body = @EmailMessage,
    			@body_format = 'HTML',
    			@importance = 'High'

    Open in new window

    Hope that answers your questions.
    LVL 1

    Author Closing Comment

    This was the best answer and worked for me..Thanks!

    Featured Post

    PRTG Network Monitor: Intuitive Network Monitoring

    Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

    Join & Write a Comment

    This is basically a blog post I wrote recently. I've found that SARGability is poorly understood, and since many people don't read blogs, I figured I'd post it here as an article. SARGable is an adjective in SQL that means that an item can be fou…
    This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
    In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…
    Here's a very brief overview of the methods PRTG Network Monitor ( offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…

    754 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    25 Experts available now in Live!

    Get 1:1 Help Now