SSIS - Execute task in foreach loop only once

0
votes

I need some help with the following issue:

Inside a foreach loop, I have a Data Flow Task that reads each file from the collection folder. If it fails while proccesing a certain file, that file is copied to an error folder (using a file system task called "Copy Work to Error").

I would like to set up a Send Email Task that warns me if there were any files sent to the error folder during the package execution.
I could easily add this task after the "Copy Work to Error" task, but if there are many files that fail the Data Flow Task, my inbox would get filled.

Instead, I would like the Send Mail Task only once (after the foreach loop completes) only if the "Copy Work to Error" task was executed at least once. Is there any way I could achieve that?

Thanks,

Ovidiu

3
foreachssis
What details do you want in the email? Only that processing failed for one or more files, or the file names and so on? - shree.pat18
A simple warning message should do it. - ovidiufelixb

3 Answers

0
votes

You could achieve this using just a boolean variable say IsError created outside the scope of the for each loop with default value as False. You can set this to True immediately after the success of Copy Work to Error task using an expression task(SSIS 2012) or an Execute SQL task. And finally your Send Mail task would be connected to the For Each loop with the precedence constraint set as the Expression - isError.

2
votes

Here's one way that I can think of:

  1. Create an integer variable, @Total outside the ForEach container and set it to 0.
  2. Create an integer variable, @PerIteration inside the ForEach container.
  3. Add a Script Task as an event handler to the File System Task. This task should increment @Total by @PerIteration.
  4. Add your SendMail task after the ForEach container. In the precedence constraint, set type to Expression, and specify the condition @Total > 0. This should ensure that your task is triggered only if the File System Task was executed in the loop at least once.
0
votes

When the error happens, create a record in a table with the information you would like to include in the email - e.g. 1. File that failed with full path 2. the specific error 3. date/time

Then at the end of the package, send a consolidated email. This way, you have a central location to turn to in case you want to revisit the issue, or if the email is lost/not delivered.

If you need implementation help, please revert back.