How can I inform myself that an automatic SQL Server process has worked? I have a Windows batch file which extracts some data from a remote system and then imports it into an instance of MS SQL Server 2014. The batch file runs SQLCMD and uses SQL files to bulk insert from the text files into a staging table, tidy the fields up and move the data to a production database. 

Having finished the development of this, I would like to test the stability of the process by easily knowing when it has run successfully.


I could check the batch file error log, but it is part of a larger process which means it takes a while to find the SQL Server part.
I could send an email to myself when the process has finished, but this does not tell me if it worked properly. By this, I mean if the INSERT INTO statement from the staging table to the production table did not produce any errors.


Does anyone have any suggestions? My gut feeling is that I need to include something in the SQL statements I use to import the data. Thanks!
count lines/records in your textfile
import
count records after import
if count1=count2 then continue
tidy up fields  (update blablabla set changefield='new' where myfield='badinfo')
test tidy up :  select count(*) from blablabla where myfield='badinfo' and changefield&lt;>'new'
if testTidyUp-count = 0 then cleanup = correct ... continue
count records after tidy up
export to production
count records in production
if count3=count4 then ... let mysql know all is 'ok'


All the else ... mail that something is wrong