A sample Windows script for SQL Server is as follows. The key components of the script are described below:
Tip
Parameters can also be added to Scripts to facilitate deployment processes or environment changes without the need to regenerate scripts. Parameters can be added to scripts of Load and Export tables.For example: add $P<ParameterName>$ to the script where $P$ is the parameter indicator as shown below. Before adding the Parameter to the script, create the desired parameter in Tools > Parameters > Add Parameter Source (context menu).
The script makes use of a number of environmental variables. These variables are acquired from both the Table and Connection properties. These variables are established in the environment by either WhereScape RED or the scheduler. If the script is to be executed outside of WhereScape RED or scheduler control, then these variables need to be assigned.
The first section of the script defines the variables. The second section provides a timed wait for the load file to arrive. By default, the WAITSECS variable is set to zero so that no wait occurs. This can be set to a number of seconds that the script is to wait for the file to arrive.
Once the wait has completed, either through a time expiry or through the location of the file, we check that the file is present, and if not found report back a warning. This warning can be changed to an error by changing the first echo statement to "-2". See the syntax section for more information.
When a trigger file is specified, the script looks for a trigger file, and will exit with the specified status if the file is not found. The following code is included if a trigger file is present.
Such a file (trigger) contains control information about the main file to be loaded and arrives after the main file to indicate that the main file transfer has completed and that it is OK to load.
This section loads the contents of the trigger file into the Parameters table, so that the table can be validated. See the section on Post Load procedures for an explanation on how trigger files are used to validate a load file.
This section calls isql to invoke Bulk Insert to load the file. It makes use of a temporary file to build as a control file and then calls isql to load the data. Note that the load is actually in a for loop. Wild card file names can be used to load multiple files. Each file to be loaded must have the same format.
Note
The data being loaded is appended to the database table. As part of the scheduler run the load table is truncated if the property for truncation is set. In this way multiple files can be loaded into the database table.
Tip
If this script is to be executed outside the control of the WhereScape RED scheduler then a truncate statement may need to be performed on the database load table. This would normally be placed before the 'for loop' and would look something like the following: isql -S %SERVER% -d %DATABASE% -U%USER% -P%PWD% -n -Q "truncate table load_forecast" -o %FILELOG%
This next section handles the rename and potential looping. The first block of code renames the file and also the trigger file if appropriate. This code is only generated if the rename fields in the file attributes are populated.
The goto label_load statement 9 lines from the end can be used if all the files in a wild card file load are required. Simply uncomment this goto statement and the script will load each file in the wild card.
