SSIS Job to Email Based on a Query Result

Question

I have a query that returns 4 columns: DATE QTY1 QTY2 MATCH

if the two qty's match, then the MATCH column will be 'Y', if not, then 'N'

I am trying to figure out how to make an SSIS job run on a schedule every morning that let emails me when it gets an 'N' in the MATCH column.

I have tried a few different things in SSIS, such as Conditional Split, but I am new to SSIS and can not figure out how to accomplish this. Any ideas? or is there another way to go about this?

Just one row of data? Many rows of data? Is there any reason to pull data where match = Y? Do you need to know about every row that has a match = N or just in today's execution, there was at least one mismatch? — billinkc
It is just the one row of data. Now that you say that, I reckon I don't need to bring back any data if it matches. Then all columns will be Null unless it doesn't match. It just brings back yesterday's numbers and sees if it matches. — jrussin

billinkc billinkc · Accepted Answer · 2020-05-08T19:33:33

Seems like a job for sp_send_dbmail It's likely already set up on your server, talk to your DBAs about getting access to it.

The following query can either be used directly in a SQL Agent job step or you can use it within an Execute SQL Task in SSIS

We test to see if the condition is met (if exists) and if so, then we're going to send an email. We're going to inline the results of the query directly into an email so you can see that criteria was met and the details of what happened

IF EXISTS
(
    SELECT * FROM dbo.QuerySource AS QS WHERE QS = 'N'
)
BEGIN
    EXECUTE msdb.dbo.sp_send_dbmail @recipients = '[email protected]'
    ,  @subject = 'Mismatch in data'
    ,  @body = 'Bad things happened today'
    ,  @query = 'SELECT * FROM dbo.QuerySource AS QS WHERE QS = ''N''';
END

SSIS Job to Email Based on a Query Result

1 Answers