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!