LGD has released a tool for setting up and managing scheduled scripts. It is designed to replace the Windows Task Scheduler. It allows users to schedule scripts for importing files and updating the database either every 30 min, every hour or daily. It pauses the MCM service before running each script and restarts it when the script is complete.
Installation and Setup
The tool is installed in the Channergy data folder and consists of two applications, the Channergy Task Scheduler which is the user interface that allows you to manage the tasks as well as monitoring them in the ScriptLog table. There is a second application, ChannergyTasks that is set up as a service that runs the tasks in accordance to the set schedules.
Setting up the Service
The installer will install the ChannergyTasks.exe as a service in the default folder. However, if your default location is not a standard location, then the file path will have to be edited in the registry editor. The following instructions are for that.
- From the search bar type in Regedit. This will launch the registry editor.
- Locate the following key:\HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\ChannergyTasks\Parameters
- Double-click on the Application entry, this will open up the Edit String window
- In the Value data field change the path to be the correct path to the ChannergyTasks.exe file.
- Click OK to close the Edit String window.
- Close the Registry editor.
Scheduling a Task
The Channergy Task Schedule is installed with some default scripts that are commonly used and these instructions are designed to set up one of those tasks to run on a schedule. However, once any task has been created in the tool the process is the same.
- Launch the Channergy Task Scheduler from the Tools menu in Channergy.
- In the Select Script combo box select the script you want to schedule.
- Set up the following settings
- Check the Is Active box
- Select the frequency
- If you select daily the application will prompt you for a time of day.
- Key in the time that you want the script in military time. In the example above 22:59 is 10:59 PM.
- Click on the Save Settings button.
- The Settings tab will look similar to this.
- Note, if the script has never been run on your system the Last Run and Next Scheduled fields will say Never.
There are generally two classes of scripts that can be written in the task scheduler. One is that imports files from a dedicated folder and processes them and another that simply runs a series of SQL commands to update the database and optionally export files.
The Channergy task scheduler has the ability to import files (and subsequently processing them) from a file folder on the server. It is designed to import any file that is in the specified folder with a specified file extension. The import file setup is done in the Import Table tab.
The Import Table tab contains the following fields.
The SQL Window
This is the window that stores the SQL commands for creating the table that you will be importing. It generally contains a DROP TABLE command and then a CREATE TABLE command. This is where the format of the imported table is specified, the table field names need to match the field headers of the imported file(s). For more information on the CREATE TABLE command go to here.
This button will check the syntax of the SQL commands for dropping and creating the tables. When you click on the button a Check SQL window opens showing any errors that the command parser found. Under normal circumstances the window will look like this
If there are any errors in your SQL you will get a message similar to this one.
Import Table Name
This field is used by the application to tell it what is the table name that the data is being imported into using the IMPORT TABLE command. It needs to match the table name that is specified in the related CREATE TABLE command.
Import Folder Path
This is the field that tells the scheduler where to import the file(s) from. The task scheduler will import all files that are in the selected folder that match the Import File Filter field. Once the files are imported they are moved to a folder called Processed so that the same file is not imported multiple times. To set the Import Folder Path click on the Select Path button, this will open a file browser.
Import File Filter
The Import File Filter field allows you to specify the file type you want to import. By default the application will try to import all files that are in the Import Folder Path. If there are files that do not match the specified file format, the application will not process the files correctly so it is important that either only one type of file is placed in the import folder path or that the Import File Filter is set to import the desired file types.
Note: As of this time, the file import utility will only import comma separated variable (CSV) files. More options will be added in the future.
Regardless of whether or not the scheduled task imports files or not all scheduled tasks will have commands in the Process tab. This is where the actual processing takes place.
This window will contain the SQL commands that the task scheduler runs each time. For more information on the DBIASM SQL syntax go here.
Check SQL Button
This button will check the syntax of the SQL commands for processing the data. When you click on the button a Check SQL window opens showing any errors that the command parser found. Under normal circumstances the window will look like this
If there are any errors in your SQL you will get a message similar to this one.
Managing your Scripts
The following discussion deals with the buttons on the bottom of the Settings tab
Adding a New Script
When you click on the Add New Script button, a dialog box will prompt you for a new script name.
When you click on the Add Script button a new record will be added to the ChannergyScripts table and will be in the Select Script combo box.
The Settings tab will look similar to this.
By default the Is Active checkbox is set to unchecked because the new script has no SQL code to run. Once the SQL has been added and tested you can check the Is Active box and it will show you the scheduling options.
Deleting a Script
If you want to delete a script, do the following:
- Select the script that you want to delete from the Select Script dialog box
- Click on the Delete Script button. A dialog box will prompt you to confirm that you want to delete the selected script.
- Clicking on the Yes button will permanently delete the selected script.
Copying a Script
In some cases you may want to use this option if there is an existing script that can be used as a template for a new one.
- Select the script you want to copy from the Select Script combo box.
- Click on the Copy Script button, a dialog box will show and allow you to create a new script.
- When you click on the Copy Script button, the new script is completed with all the same settings and SQL as the original one.
As the name suggests this button saves all of the settings for the scheduled task, including the information stored in the Import Table and Process tabs. It is important that you click on this button after you have successfully updated the SQL in any of the tabs as the changes will not be saved.
The Test Script button will do everything that the script is designed to do is a test mode. It will pause the MCM run the Import Table commands (if specified) and then processes them. If the Channergy Task Scheduler is not set to run as administrator, the MCM will not stop and the Test Script window will show a message similar to: 10:57:12: Stop MCM failed:Cannot open MCM service on computer ‘.’.
Regardless, the Test Script button will test the whole package.
Tracking the Status of your Scripts
The Channergy Task Scheduler keeps a log file of the active scripts in the system. It is located under the Log tab and loads the log information for the script selected in the Select Script combo box in the Settings tab.