The details of the program are in part one.
At this point the program has run without any issues for many months now. Suddenly it decided that was too long and no longer ran. I had the fun task of finding out why and how to fix it.
If the files that are transferred in FTP are invalid it would crash. Since the first step is emptying the tables of data this left the database empty. The files were only wrong once or twice. They weren't the issue here.
I had no logs except for what the job history said. The program did not have any error handling or logging.
I could see in the job history that it looked like the SQL was timing out. That raised a lot of questions for me. Why would a single insert statement timeout? Why now and not always? During debugging it never failed. What was different about my machine?
One of the first changes I made was to remove the timeout from the SQL Command. Next I created some logging to get actual details if it does fail. At this point I also added some retry logic and error handling. It shouldn't time out since the timeout is set to infinity but just in case there are errors they will be caught and give some details. If the failure was a fluke it would insert on the retry probably. Finally, the biggest change, I spun up a thread for each function. The order of the data did not matter and as long as the insert statements ran it was fine. Threading always sounds fancy to me but C# has made it incredibly easy now to do it.
Even with the infinite timeout I still received a timeout message. Makes no sense to me but it proved my logging worked and gave me concrete information. I also found that even with threading the program was taking up to 6 hours to run. It never took that long in the past even with the same file lengths. I had a new plan and knew these updates were a temporary measure for the client until I could get bigger changes in.
Next post will detail the bigger fixes.