In the previous blog we created a job that would read an Excel File and use the information stored to login to the FTP server of a list of websites in order to upload the update files of a CMS site.
How about adding a few bits and pieces to make this Job provide us a with a log of the updates made so that we can keep track of our updates.
Let’s see how that could be achieved…
Edit Excel File
To start with you need to add an extra worksheet in your Excel File; we’ll call it UpdateHistory, and will name three columns:
- Start Time
- Finish Time
Having added the new Worksheet, and once we have finished getting the values we need, having read the Spreadsheet and got our Data Table, we simply add a "Set Active Worksheet" action, to define that from this point on we want to make use of the second worksheet in our Excel file. We also move the Close Excel Action to be the last action of the Job.
Adding Actions to the For Each Loop Block to accomplish History Archive
Now, when in Action 12, that would be prior to opening the FTP Connection, we will add a Get Current Date Time, and make sure we name the output variable to a name that will define the value it holds, which would simply be the starting point for the update. Having done that, then we add another "Get Current Date Time" action just after the Close FTP Connection action to mark the finishing point for the update. Once again, we make sure that we give the output variable a name that will help us understand the value it holds, so that we can then easily pick it from the list of variables when we'll need to use the value it holds.
Once our files upload has completed, and the folder has been renamed back to its original name (that would be the Release's name), when in Action 18 just after the "Rename Folder" action, we add a "Get First Free Column/Row" action, so that we can get the first empty line, so that we can append values to it. We shall make use of the "Write to Excel" action 4 times, one for each column we want to append data to.
Keeping a History-Update Log
- 1st one would be to append the website we have just updated. That information is held within the %CurrentRow[Website]% variable, so we configure the action to write to the 1st column and the first free line available (that is done using the %FFR% variable defined in the previous action).
- 2nd one would be to append the time that the update process started. So we enter here the %StartTime% variable as we previously defined it in action 12.
- 3rd one would be to append the time that the update process finished. So we enter here the %FinishTime% variable as we previously defined it in action 16.
- 4th one would be to append the Release update we uploaded. That would be the name of the file that triggered the job initially, so we use the %FileNameNoExtension% variable, as defined in action 2.
Having made changes to our excel file, we shall also want to save the changes made, so in action 26 where the "Close Excel" action is found, we simply open and reconfigure it to save the file before closing instead of just closing it!
You can also add exception handling to each action, so that in the event the server is down, it will inform you, or reschedule the update process, and many many more!