Send email if source and target tables record count is not matching

Other SQL Server Topics
1
  1. We would like you to add a sanity check step in the Sql Server Agent Job Step to compare the record counts in the source tables/views and the corresponding fact/dimension tables every time the job is run. The record counts at the source and the target should match after the package is run. In case of mismatch, we would need an alert via email and this will enable us to perform necessary analysis and take an appropriate action immediately.

Currently this check is required for specific tables .
Can anyone suggest me on how to compare record count between source and target? and how to send an email thru sql server agent if there is any mismatch ?.

  1. Also, Since the delta identification is based on the last updated/change date and if we miss any records (for some reason) during any run, the current process(look up on primary key b/w src and target) wouldn't include them in the next run and very soon the source and the target would go out of whack before anyone realizes it.

How to find out these records after job/package run and need to consider those in next run?

2

Cant you deal with this in the ETL processing, you could use a script command as part of the flow ?
You can create a SQL Agent job with something like this:

Use msdb
Go
EXEC sp_send_dbmail 
@profile_name='JoeBloggs',
@recipients='JoeBloggss@myserver.co.uk',
@subject='Record No Match Counts',
@body='Compare record counts.',
@importance = 'High',
@query = 'Use Test
Go
SELECT 
              COUNT(s.[RecID]) as SourceCount
			  COUNT(t.[RecID]) as TargetCount
       FROM [Source] s
       JOIN [Target] t
              ON s.[SourceID] = t.[TargetID]
       WHERE COUNT(s.[RecID] <> COUNT(t.(RecID)'
       
       ,
 @attach_query_result_as_file = 0 ;
 Go
3

I can use SSIS for this.
I need to sent email with below information (tablename, source and target count). when row count is not matching b/w src and trg then only i need to send email.

How to achieve this in SSIS?

4

There are different ways to go about this, one is using the SQL Agent Job based on conditions as above example or through SSIS with a combo of tasks such as Script Comp - Conditional Split - success/failure constraints to email...based on script.