ssis error handling

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..
Who is Participating?
Jason Yousef, MSConnect With a Mentor Sr. BI  DeveloperCommented:

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.
SThayaTechnical MAnagerCommented:
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.
cheryl9063Author Commented:
"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?
Jason Yousef, MSSr. BI  DeveloperCommented:

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 
-- 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.
cheryl9063Author Commented:
This was the best answer and worked for me..Thanks!
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.