The following table shows the pushdown types for each database to which you can push the Update Strategy transformation:
Microsoft SQL Server
Source-side, Target-side, Full
Use the following rules and guidelines when you configure the Integration Service to push Update Strategy transformation logic to a database:
The generated SQL for an Update Strategy transformation with an update operation can be complex. Run the session with and without pushdown optimization to determine which configuration is faster.
If there are multiple operations to the same row, the Integration Service and database can process the operations differently. To ensure that new rows are not deleted or updated when pushed to a database, source rows are processed in the following order: delete transactions, update transactions, and then insert transactions.
If the transformation contains more than one insert, update, or delete operation, the Integration Service generates and runs the insert, update, and delete SQL statements serially. The Integration Service runs the three statements even if they are not required. This might decrease performance.
The Integration Service ignores rejected rows when using full pushdown optimization. It does not write reject rows to a reject file.
The Integration Service processes the Update Strategy transformation if any of the following conditions are true:
If the Integration Service cannot push the update strategy expression to the database. For example, if the expression contains an operator that cannot be pushed to the database, the Integration Service does not push the expression to the database.
The transformation uses operations other than the insert operation and the Integration Service cannot push all transformation logic to the database.
The update strategy expression returns a value that is not numeric and not Boolean.