Sql-server – How to inform theself that an automatic SQL Server process has worked

automationimportprocesssql server

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!

Best Answer

  1. count lines/records in your textfile
  2. import
  3. count records after import
  4. if count1=count2 then continue
  5. tidy up fields (update blablabla set changefield='new' where myfield='badinfo')
  6. test tidy up : select count(*) from blablabla where myfield='badinfo' and changefield<>'new'
  7. if testTidyUp-count = 0 then cleanup = correct ... continue
  8. count records after tidy up
  9. export to production
  10. count records in production
  11. if count3=count4 then ... let mysql know all is 'ok'

All the else ... mail that something is wrong

Related Question