asked
SSIS to copy multiples files and send only one email
I am trying to copy today’s files (multiple files) from one folder to another folder and just send one email after successful copying. First I am checking a table for error and if there are no errors then send the files. I also want to send email if it doesn’t copy files because of error. I am using foreach loop container to copy files but when I use send mail task inside the loop, it sends me email for every files that are on the source directory. Everything is working fine except for the email. Could anyone help me with this? Where should I put the send email task? I need to send only one email for success or failure of copying files. ![alt text][1] [1]: /storage/temp/498-ssis.jpg
You should put two `Send Mail Task` after the `Foreach Loop Container`. One will be connected to the `Foreach Loop Container` using the Success Precedence constraint an dthe second one will be connected using the Failure Precedence constraint. In that case if there will be a failure inside dhte Foreach Loop, the Send Mail Task connected with Failure constraint will be fired and if everything will pass successfully, than the Send Mail Task connected using the Success Precedence constraint will be fired. If you want that the failure email is sent, but do not wish the complete package ends with failure, you will have to set `MaximumErrorCount` on the package level to 0. (default is one, which causes that the whole package fails in case of any error inside the package).
When I tried this it sends the "success" email regardless of errors in the table. I have an audit table which has error levels based on updates I am doing everyday. I am using execute sql task to check make sure there are no error levels in the table. If there are errors then it won't copy any files. If there aren't any error levels then copy the files. SSIS package runs successfully regardless of error code in the sql code. My goal is to send email when there are error code in my sql table and also when there are no error.
By the failure I have meant failures which occurs inside the ETL path, like the file copying fails due to inssufitient rights etc. In such cases this will work as described. If you have some additional logic for testing failures, you can set variable identifying the Success or Number of errors. If your `Execute SQL Task` checks for this and your precedence constraint to the Sequence check for the success using expression, then simply add a `Script Task` connected to your `Execute SQL Task`. You will pass the result of the check also to this Script Task and in case of failure it will increment the ErrorCount variable. (The Script task will be parallel to your Sequence). Finally on the Precedence constraint betweek the `Foreach Loop Container` and the SendMail task you can add expressio to check number of errors. If there are errors, the `Send Mail Task` simply will not be executed. In that case you can have two send mail task connected with Success Precedence constraints, one which will check for Number of Errors = 0 and the other one Number of Errors > 0.
It could be possible to achieve the incrementing of the Number of Errors directly in the Execute SQL Task, but it depends on your code there and whether you are able to pass and return additional values.
I am kind of new to SSIS and might not be making any sense. I am still having the same problem. It only sends the success email. Let me try to explain step by step: 1.Execute SQL Task is checking for (count) number of errors from a table. The precedence constraint between sql task and sequence is evaluating an expression which is if number of error is 0 then move the files. When I have error in the table the ssis package runs successfully but it doesn’t move any file which is what I want. But my problem is since the package runs successfully it sends the success email. Am I missing something here? Should I do something else? I am attaching the image and thanks for the help.![alt text][1] [1]: /storage/temp/503-ssis.png
As I have mentioned, the Failure Precedence constraint will trigger only in case of the Foreach loop failure. In case you have error detection and the foreach loop do not fails, than you have to have both precedence constraint with Success and handle the execution of Send Mail task by expressions on the Precedence constraint. Something like this: ![SSIS][1] [1]: /storage/temp/504-ssis.png
9 People are following this question.
