Error output logic

Hello,

I have a simple SSIS package running under SQL 2005 SP2a.

In the data flow, I have several lookup transforms with error outputs. Each error output links to its own audit transform and then writes data to its own flat file destination.

After the data flow is complete, my control flow will then use a ForEach file container to mail the flat files to the source system data owners with a message about incomplete/inconsistent source data. I am effectively providing feedback to the source owners so that we may improve the quality of data that gets sent to us.

My problem is that the flat file (which contains the offending rows) seems to get created everytime even when there were no errors in the lookup in question. Thus my ForEach container will always send a mail to the data source teams even if there were no errors as the error flat file will always exist albeit empty.

How can I stop this happening? How can I only create flat files when there really were errors? How can I prevent the source teams from receiving feedback emails when there is no reason to?

Thanks in advance
MGale1

Before your send mail task, add a script task to interrogate the flat file to see if it is greater than zero bytes. If so, then set a variable to 1 else set it to 0. Then hook the script task up to the send mail task and use a precedence constraint to execute the send mail task if the variable is equal to 1.