When you configure a session to load data to a relational target, you define most properties in the Transformations view on the Mapping tab. You also define some properties on the Properties tab and the Config Object tab.
You can configure the following properties for relational targets:
Target database connection.
Define database connection information.
You can define target properties such as target load type, target update options, and reject options.
Truncate target tables.
The Integration Service can truncate target tables before loading data.
You can configure the session to retry deadlocks when writing to targets or a recovery table.
Drop and recreate indexes.
Use pre- and post-session SQL to drop and recreate an index on a relational target table to optimize query speed.
The Integration Service can load data to targets based on primary key-foreign key constraints and active sources in the session mapping.
You can specify bulk mode when loading to DB2, Microsoft SQL Server, Oracle, and Sybase databases.
You can define the following properties in the session and override the properties you define in the mapping:
Table name prefix.
You can specify the target owner name or prefix in the session properties to override the table name prefix in the mapping.
You can create SQL commands and execute them in the target database before loading data to the target. For example, you might want to drop the index for the target table before loading data into it.
You can create SQL commands and execute them in the target database after loading data to the target. For example, you might want to recreate the index for the target table after loading data into it.
Target table name.
You can override the target table name for each relational target.
If any target table or column name contains a database reserved word, you can create and maintain a reserved words file containing database reserved words. When the Integration Service executes SQL against the database, it places quotes around the reserved words.
When the Integration Service runs a session with at least one relational target, it performs database transactions per target connection group. For example, it commits all data to targets in a target connection group at the same time.