Configuring the Target Update Override

By default, the Integration Service updates target tables based on key values. However, you can override
the default UPDATE statement for each target in a mapping. You might want to update the target based on non-key columns.

For a mapping without an Update Strategy transformation or a Custom transformation with the update strategy property enabled, configure the session to mark source rows as update. The Target Update option only affects source rows marked as update. The Integration Service processes all rows marked as insert, delete, or reject normally. When you configure the session, mark source rows as data-driven. The Target Update Override only affects source rows marked as update by the Update Strategy or Custom transformation.

For example, a mapping passes the total sales for each salesperson to the T_SALES table.
The Designer generates the following default UPDATE statement for the target T_SALES:

UPDATE T_SALES SET EMP_NAME = :TU.EMP_NAME, DATE_SHIPPED = :TU.DATE_SHIPPED, TOTAL_SALES= :TU.TOTAL_SALES WHERE EMP_ID = :TU.EMP_ID

Because the target ports must match the target column names, the update statement includes the keyword:TU to specify the ports in the target transformation. If you modify the UPDATE portion of the statement, be sure to use :TU to specify ports.

Overriding the WHERE Clause

You can override the WHERE clause to include non-key columns. For example, you might want to update records for employees named Mike Smith only. To do this, you edit the WHERE clause as follows:
UPDATE T_SALES SET DATE_SHIPPED = :TU.DATE_SHIPPED,
TOTAL_SALES = :TU.TOTAL_SALES WHERE :TU.EMP_NAME = EMP_NAME and
EMP_NAME = 'MIKE SMITH'.

Steps to Enter a Target Update Statement

Use the following procedure to create an update statement.
To enter a target update statement:
1. Double-click the title bar of a target instance.
2. Click Properties.
3. Click the Open button in the Update Override field.
The SQL Editor displays.
4. Select Generate SQL.
The default UPDATE statement appears.
5. Modify the update statement.
You can override the WHERE clause to include non-key columns.
Enclose all reserved words in quotes.
6. Click OK.
The Designer validates the SQL when you save the mapping.

Note:

1. If you use target update override, you must manually put all database reserved words in quotes.
2. You cannot override the default UPDATE statement if the target column name contains any of the following characters:
' , ( ) < > = + - * / \ t \ n \ 0 <space>.

Hope you enjoyed it. Please leave your comments.