Creating Database Target Activity

The Database Target activity provides the ability to specify a database as target. It is recommended that you use Advanced Database Target Activity. With this, you can specify multiple tables of a database as target. Click here to know more about Advanced Database Target Activity.

Prerequisites

Database Info activity and Database Schema must be created before creating Database Target activity.
This section describes how to create a Database Target activity using the following details as an example:
 

Steps to create a Database Target Activity

1. On the Adeptia Suite homepage, click the Develop tab.

2. Go to Services > Target and then click Database. The Manage Database Target screen is displayed (see Figure 283).

   

   Figure 283: Manage Database Target

3. Click the Create New link. The Create Database Target screen is displayed.
4. Enter the name and description of the new Database Target in the textboxes Name and Description respectively.

5. Select the database info activity and database schema activity from the dropdown lists Database Info and Schema Name respectively (see Figure 284).
   
   

   Figure 284: Create Database Target

   To learn how to create Database Info activity, refer to Creating Database Info section in Administrator Guide. To learn how to create Database Schema activity, refer to the Creating Database Schema Activity section.

   Database target can insert/update current date in the database. This current date is added in the input XML in two ways:

   • Source XML

   • Mapping

     It can be added through the source XML that has the current date already defined in its code.

     It can be added through mapping, if current date is defined as a constant in the Date field, or as a variable which has the value "Current Date". In this case, when mapping is done, then database target accesses the current date and inserts/updates it accordingly.

6. Database Operation specifies how data records are inserted into database tables. Select one of the following database operations:
    
   1. • Insert
      • Update
      • Insert/Update
      • Update/Insert

    The effect that the selection has on the database is listed in the table below.

   A field defined as a primary key at the schema level does not qualify to act as a primary key for the database. You need to set that field as a primary key at the database level too. 

   Table 2: Effect on Database based on the selected Database Operation

    

   Database Operation	Effect on Database
   Insert	This option is selected when you want to insert records into the database tables. If records already exist in the database table, new records are added in the database table along with existing records. When a column e.g. CompanyName of database table is marked as Primary Key, more than one record cannot exist in the database table for the same Company Name. In this case if data of a company i.e. Microsoft Corporation already exist, insert operation fails and data is not inserted into the database.
   Update	This option is selected when you want to update the existing record. To use updated option a column of database must be marked as Primary Key. When Update option is selected, database target first checks which column of the database table is marked as Primary Key. Suppose CompanyName column is marked as Primary Key. Now database target check whether data of a company e.g. Microsoft Corporation already exist or not. If data for Microsoft Corporation already exists, database target updates the existing records. If none of the column is marked as Primary key, Update operation fails.
   Insert/Update	This option is selected when the database target first tries to insert the data into database table. If insert operation fails, database target tries to update the data.
   Update/Insert	This option is selected when the database target first tries to update the database table. If update operation fails, database target tries to insert the data.

   If the specified database operation fails on any source data, error records are created.
    
   To learn, how to use Process Designer, refer to the section Using Process Designer.
   To learn how to handle error records, refer to the section Handling Error Records.

   When an excel file is used as a database target, only Insert operation is supported. The Update, Insert/Update and Update/Insert operations are not supported.
   When an excel file is used as a database target, the error records are not generated in case of data type mismatch.

7. Click the Save button.