We can use the session configurations to update the records. We can have several options for handling database operations such as insert, update, delete.
During session configuration, you can select a single database operation for all rows using the Treat Source Rows As setting from the ‘Properties’ tab of the session.
- Insert: – Treat all rows as inserts.
- Delete: – Treat all rows as deletes.
- Update: – Treat all rows as updates.
- Data Driven :- Integration Service follows instructions coded into Update Strategy flag rows for insert, delete, update, or reject.
Once determined how to treat all rows in the session, we can also set options for individual rows, which gives additional control over how each rows behaves. We need to define these options in the Transformations view on mapping tab of the session properties.
- Insert: – Select this option to insert a row into a target table.
- Delete: – Select this option to delete a row from a table.
- Update :- You have the following options in this situation:
- Update as Update: – Update each row flagged for update if it exists in the target table.
- Update as Insert: – Insert each row flagged for update.
- Update else Insert: – Update the row if it exists. Otherwise, insert it.
- Truncate Table: – Select this option to truncate the target table before loading data.
Steps:
- Design the mapping just like an ‘INSERT’ only mapping, without Lookup, Update Strategy Transformation.
- First set Treat Source Rows As property as shown in below image.
- Next, set the properties for the target table as shown below. Choose the properties Insert and Update else Insert.
These options will make the session as Update and Insert records without using Update Strategy in Target Table.
When we need to update a huge table with few records and less inserts, we can use this solution to improve the session performance.
The solutions for such situations is not to use Lookup Transformation and Update Strategy to insert and update records.
The Lookup Transformation may not perform better as the lookup table size increases and it also degrades the performance.