Data Export

The Data Export Plugin updates a single table in the designated database using OLE Db. A Data Group in the same DSD contains the data to be updated. Each row in the Data Group is updated to the table. Field names must match, case sensitive. 

Updates will not be performed on a “schema” run of the DSD, but will be updated on a test run. 

Transaction roll back is NOT available. Following clearance of duplicate records and the specified ClearSQL command, each record from the Data Group is added individually to the table. 

Multiple instances of the Data Export plugin may be executed from the same DSD. 

Parameters 

Connection  The id of a the fAdmin source for the database to be updated. Must have setting “update” set to “true” 
Table  The table to be updated 
Operation  The action to be taken
– clear – clear all current records in the table
– replace – replace existing records based on the unique key
– append – add to the table records (e.g. audit) 
DataGroup  The data group in the DSD that contains the data to update 
UniqueKeyField  Optional. The field in the table that represent the unique key. Used when replacing data to remove existing records to be replaced. 
ClearSQL  Optional. SQL WHERE clause that clears selected records from the table before updating. 

Process 

The Data Group, Connection and target table are validated. Any error stops execution. 

Pre-Clear Records 

Depending on the “operation”, the following actions occur: 

clear 

All records in the target table are cleared. 

replace 

If the ClearSQL is specified it is run as “DELETE FROM [table] WHERE [ClearSQL]“. No checks are made on the clause. 

If the UniqueKeyField is specified, each record in the target table with a matching value of the UniqueKeyField in the Data Group is deleted in turn. 

Note that ClearSQL is faster and also capable of clearing records that no longer apply, whereas UniqueKeyField can only remove those that match new records. 

append 

No action is taken to clear records as all records will be appended.  

Update 

Each record in the Data Group is added to the target table.  

Errors will occur on primary key violations, mis-matched fields and missing required fields. 

Each record is updated individually and can fail individually. Errors are reported in fTest but not in LoadErrors