ETL2 is a new bulk data loader introduced in Connect 10R4. ETL2 is similar to the
Informatica bulk loader in that it has a two-phase structure:
- In Phase 1, data are loaded from any source, such as flat files or external
databases, into predefined interface tables.
- In Phase 2, data are copied from the interface tables into Connect's operational
tables. Once the data have been copied, Connect's campaign management system
will process the data automatically.
ETL2 has the following differences from the Informatica bulk loader:
- ETL2 has no dependency on a third-party ETL package. Phase 2 is defined using
native stored procedures and any ETL package may be used to load the interface
tables in Phase 1.
- ETL2 supports concurrent data loads.
ETL2 Installation
ETL2 package is installed automatically by updatedb.
ETL2 Interface Tables
ETL2 defines the following interface tables.
- ETL2_CUSTOMER_MASTER
- ETL2_CUST_ATTRIBUTES
- ETL2_CUST_PREFERENCES
- ETL2_CUST_SUBSCRIPTION
- ETL2_EVENT_QUEUE
- ETL2_EVENT_QUEUE_DETAIL
- ETL2_EVENT_QUEUE_ATTACHMENT
Each ETL2 has the same schema as the previous ETL
tables used by the Informatica loader. However, each
ETL2 table has an additional JOB_ID field that
designates which data load job each record belongs to.
There is an error table for each interface table. The error
tables have the name prefix ERROR2. ETL2 defines the following
error tables:
- ERROR2_CUSTOMER_MASTER
- ERROR2_CUST_ATTRIBUTES
- ERROR2_CUST_PREFERENCES
- ERROR2_CUST_SUBSCRIPTION
- ERROR2_EVENT_QUEUE
- ERROR2_EVENT_QUEUE_DETAIL
- ERROR2_EVENT_QUEUE_ATTACHMENT
When ETL2 Phase 2 runs, it identifies any bad records
and copies them into the ERROR2 tables. Each ERROR2 table
also has an ERROR_REASON column which describes why each
record could not be loaded.