- 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 ?.
- 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?