• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 407
  • Last Modified:

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..
0
cheryl9063
Asked:
cheryl9063
  • 2
  • 2
1 Solution
 
SThayaCommented:
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.

http://msdn.microsoft.com/en-us/library/ms141679.aspx
http://www.c-sharpcorner.com/blogs/5164/error-handling-in-ssis-packages.aspx
0
 
Jason Yousef, MSSr. BI DeveloperCommented:
Hi,

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"

DFT
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.
0
 
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?
0
 
Jason Yousef, MSSr. BI DeveloperCommented:
Hi,

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]
GO
/****** Object:  StoredProcedure [etl].[sendEmailAlert]    Script Date: 08/21/2012 08:43:21 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO



ALTER PROCEDURE [etl].[sendEmailAlert]
(
@EmailSubject		VARCHAR (100),
@EmailMessage       VARCHAR (500)

)
AS
BEGIN


DECLARE
@EmailRecipients	VARCHAR (300),
@CopyRecipients		VARCHAR (100),
@EmailProfile		VARCHAR (100)

SET @EmailRecipients = 'test@email.com'
SET @CopyRecipients = 'copytoemail@test.com'
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'
		
END 

Open in new window



Hope that answers your questions.
0
 
cheryl9063Author Commented:
This was the best answer and worked for me..Thanks!
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now