Sign in
 
Home20122008 R2200820052000LibraryForums
SQL Server >
> 
> 
Send an email when ForEachLoop Fails

Answered Send an email when ForEachLoop Fails

  • Friday, August 03, 2012 3:47 PM
     
     
    Sign In to Vote
    0

    Hi Gurus,

    At Present Im looping through Excels and loading into db. But I have a requirement that, lets say in future if a file comes with different excel format my pkg fails.

    Lets say today I have 10 files in a sourcefolder and 10th file is in different(wrong) format, After looping through and moving 9 files to Processed folder my pkg failed at 10th file. so, Here, I need to send the user an email mentioning that the job has failed at this particular file.

    Please help me with this.

     

All Replies

  • Friday, August 03, 2012 4:08 PM
    Moderator
     
     Answered
    Sign In to Vote
    1

    precedence constraints (those arrows between task) can also be used for error flows (Double click it and check the Constraint options => Value => Failure). If a tasks fails it will continue with the red error flow... 

    Or you could use event handlers to do something when there is an error.

    Please mark the post as answered if it answers your question | My SSIS Blog: http://microsoft-ssis.blogspot.com | Twitter

    • Proposed As Answer by DotNetMonster Friday, August 03, 2012 4:12 PM
    • Marked As Answer by Spartaa Tuesday, August 14, 2012 12:45 PM
    •  
     
  • Friday, August 03, 2012 4:32 PM
     
     
    Sign In to Vote
    0
    Here is an example of your scenario. As SSISJoost mentioned either use Precedence Constraints or Event Handlers for this scenario. Personally would use Precedence constraints as it give a graphical representation of the errored flows whilst Event handlers are somewhat "hidden". A preferred approach strictly from an operations perspective.

    http://btsbee.wordpress.com/

     
  • Friday, August 03, 2012 5:46 PM
     
     
    Sign In to Vote
    0

    precedence constraints (those arrows between task) can also be used for error flows (Double click it and check the Constraint options => Value => Failure). If a tasks fails it will continue with the red error flow... 

    Or you could use event handlers to do something when there is an error.

    Please mark the post as answered if it answers your question | My SSIS Blog: http://microsoft-ssis.blogspot.com | Twitter

    Thank you for yout time.

    Well, I got it working. But my Message should include something about the filename at which my FELoop failed. Is it possible? If yes, how?

    Please explain me.

    • Edited by Spartaa Friday, August 03, 2012 6:35 PM
    •  
     
  • Friday, August 03, 2012 8:37 PM
    Moderator
     
     
    Sign In to Vote
    0
    What was your solution? If you added a send mail task outside the loop, the variable from the loop contains the last filename (the one that failed).

    Please mark the post as answered if it answers your question | My SSIS Blog: http://microsoft-ssis.blogspot.com | Twitter

     
  • Friday, August 03, 2012 8:53 PM
     
     
    Sign In to Vote
    0
    What was your solution? If you added a send mail task outside the loop, the variable from the loop contains the last filename (the one that failed).

    Please mark the post as answered if it answers your question | My SSIS Blog: http://microsoft-ssis.blogspot.com | Twitter

    Mi send my task is inside the loop like below: So, how to include that filename in the message now?

     
  • Monday, August 06, 2012 1:06 PM
     
     
    Sign In to Vote
    0

    Hi all,

    I found the variable which holds the file name, but Is there any possibilities that I can have some this User:Filename variable with some custom message without any code.

    Please help me with this.

     
  • Sunday, August 12, 2012 4:15 AM
    Moderator
     
     Answered
    Sign In to Vote
    0

    Hi Spartaa,

    You could build the email message using an Expression in the Send Mail Task. Click the Expressions page, and add an expression for the property "MessageSource". Please note that you need to cast everything to string to make it work. Please refer to Todd C's reply in the thread: http://dbaspot.com/sqlserver-dts/409138-ssis-send-mail-task-format.html

    Please feel free to ask if you have any question.

    Thanks,
    Eileen

    • Marked As Answer by Spartaa Tuesday, August 14, 2012 12:45 PM
    •