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

Question

Hi Guys,

I need your help. I have a query below. i wanna use this query in SSIS package which would run every week. All this query doing is, truncate table PlanFinder.InvalidAwps and load 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 dont know how 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

Thanks. Appreciate any help

Answer 1

Hi

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

I am not sure about the how your 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 failre of that task use send email task with your required email either derived or variable.

thanks

prav

Answer 2

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.