SSIS Help : How to Send Email if invalid Records Found from a query

I want to use the below query in an SSIS package which would run every week. All this query is doing is truncating table PlanFinder.InvalidAwps and loading it with new Invalid records. Now, how do I use this query in SSIS package to send email if any invalid record found in invalidAwps table? I can use execute sql task but don'st know how the package would send email if any invalid record found in InvalidAwps table.

truncate table [PlanFinder].[InvalidAwps] 
go 

insert 开发者_开发知识库into [PlanFinder].[InvalidAwps] 
    (Ndc, AwpUnitCost) 
    SELECT DISTINCT P.Ndc Ndc, A.Price AwpUnitCost 
    FROM 
        PlanFinder.PlanFinder.HpmsFormulary P 
        LEFT JOIN (SELECT Ndc, Price FROM MHSQL01D.Drug.FdbPricing.vNdcPrices  
                   WHERE PriceTypeCode = '01' AND CurrentFlag = 1) A 
    ON P.Ndc = A.Ndc  
    WHERE (A.Ndc IS NULL OR A.Price <= 0 OR A.Price IS NULL) 
    AND p.Ndc IS NOT NULL  

---

I do a similar thing for custom log entries (errors), simply by on the end of my package having a task collect the errors and dump them to a text file, then send the file.

Assuming you want to include the list of invalid records, you can use a Data Flow task to export the rows from InvalidAwps to a flat file (example here), then use a Send Email task to send the file as an attachment. If your Data Flow task uses a Row Count component to store the count into a variable, you can use it as a condition to not send the email (if the count is zero).

Alternatively, if you want to send a separate email per record, you could use a for-each loop, as in this example.

---

According to your requirement, you are truncating the existing table then inserting the new records into the table with your logic.

I am not sure how you are treating the a record as invalid record. For this task we consider invalid record as a record the insertion will fail.

On failure condition we need to send the email. In control flow place your logic code in one SQL task and below on failure of that task use send email task with your required email either derived or variable.