LOAD

The LOAD profile loads data from externally supplied files into the TESite database. The configuration of the LOAD profile consists of several activities: Defining the TESite table being loaded using the Import Table Setup module, FTP Setup, and the population of the LOAD profile attributes. Each of these activities will be outlined below with some of the activity descriptions being referenced to other sections of the guide.

Import Table Setup
The configuration of the TESite table being loaded is defined in the module called Import Table Definitions. The purpose of this setup is to give the application the structure of the table being loaded. This allows the application to perform the necessary data type and data length validations on the data being imported. For more detailed setup information refer to Setup Import Table Definition.

FTP Setup
Each hosted customer is given an FTP site where the data files are loaded from. The address of the FTP site is located in the Customer Portal. Data must be first placed on the FTP site as a tab-delimited .txt file with no header in the DATA folder. When a Profile is executed, the successfully loaded records will be placed in the LOADED folder. Records that failed the data load will be placed in the EXCEPTION folder.

Profile Creation
The following steps outline the process for creating a LOAD profile type.

Under Profiles in the Main Menu, select the Profile link.
The Profile screen will be displayed.
To create a new profile, select the New button.
Enter in an appropriate name for the new profile in the Name field.
In the Profile Type field, select the type LOAD.
If the Stop on Error option is set to yes, the profile will stop importing data when an error occurs. Otherwise, it will skip the error and continue processing.
Click the Save button to save the new profile.
The new profile can be seen by selecting the Profile link.

Note: Fields marked with an asterisk "*" indicate a required field.

Profile Attribute Setup
A profile of type LOAD requires a certain amount of profile attributes to be populated depending on the type of file being loaded. If the file is of type Tab-Delimited, then the EMAIL, OPTIONS, and and QRY_SECTION need to be populated; otherwise, if the file type is XML, then the XML Declare section should be populated in addition to the EMAIL, OPTIONS, and QRY_SECTION sections. The table below outlines and defines the different LOAD attributes.

Section	Key	Description	Value(s)
EMAIL	EMAIL_ON	An Option to send an Email at the end of the Load process.	C: Sends an email when the process is completed. F: Sends an email when/if the process fails. B: Sends an email if process succeeds or fails. Default: Empty
	EMAIL_TO	The email address to be sent to. (Ex. youremail@domain.com)	Default is empty.
OPTIONS	FILE_NAME	Name of the file to be loaded. This file name overrides the file name in the table import definition. This option is used only when loading one file to the database.	Default is empty.
	FILE_TYPE	The file type of the data file. The only two options are Tab-Delimited (without a heading) and XML.	TEXT-TAB XML Default: TEXT-TAB.
	UPLOAD_OPTION	How the Load will treat the existing data in the file.	D- Delete existed data and insert U -Update and insert
QRY_SECTION	GET_LOAD_TABLES	List the table names that are going to be populated by the data file. Loads only valid files from the load directory configured in the TEAIM.ini file.	If you want to only load the employee table, then the value would be: In ('EMPLOYEE')
XML_DECLARE	ATTRIBUTES	A string consisting of the attribute list for the SESSION_ELEMENT. Example agent="Databasics" coName="DB client"	Default is empty.
	ENCODING	The character set for XML. Currently the only value is: US-ASCII	US-ASCII
	SESSION_ELEMENT	An element specified by each client.	Default is empty.
	STANDALONE	If the DTD is local or specified by URL. Valid values yes or no.	Yes No

The profile information is stored in the TESite database in two tables: DB_PROFILE_HDR and DB_PROFILE_DTL. The DB_PROFILE_HDR table stores general information about the profile. The DB_PROFILE_DTL table stores profile attribute information. In each of these two tables, the Profile ID is the unique key identifier.

LOAD Custom Events
The execution of a profile is a multi-step process. There are basically three steps in the profile execution process. The first and the last steps in the process are before and after "events" that are areas for client customization that may need to occur either prior to or after the execution of the profile. The events are called SP_JAGTEAIM_BEFORE_LOAD and SP_JAGTEAIM_AFTER_LOAD respectively. Figure 1 below illustrates the process path for the execution of a profile:

The events are stored procedures that are capable of executing client-customized SQL statements either before or after each profile execution. If any code is added to either of these events, then the database administrator will have to re-compile each of the effected events. The table that follows outlines the stored procedure definitions:

Event Names: SP_JAGTEAIM_BEFORE_LOAD, SP_JAGTEAIM_AFTER_LOAD

PARAMETER	DATATYPE	DESCRIPTION
ARG_TABLE_NAME	VARCHAR(50)	The table name in the TESite database where the data in the extracted file will be loaded.
ARG_PROFILE_ID	VARCHAR(20)	The profile ID of the process. This will allow the procedure to acquire profile information from the DB_PROFILE_DTL table.
ARG_JOB_NUMBER	INTEGER	The system generated job number of the specific load process. This will be used to log any errors that occur into the TE_JOB_PROCESS_LOG table.

These pre-defined functions as well as any new functions that are created specifically for a client must follow the standards for return values that are inherent in the application. For all standard and customized functions, if an error occurs, then a function must return a value of negative one (-1). This will ensure the proper handling of error messages and "trapped" database messages. If no error occurs and the function logic is successful, then a return value of zero (0), will allow the application to continue processing the profile.

Continue to Profile Execution.